Excel Help Required

[celine]

Hi anyone

I'm really pants at excel...and I'm trying to work out a simple formula
This is what I have so far

=IF(L12=1,SUM(AT12*M12-AT12))

Now on some of the the answer comes up FALSE.. If that happens I want it to put in the cell just -AT12...

I'm not sure if I use "Else" or something like that and if I do how do I write it...Any help would be much appreciated...I really wish I even understood the Excel Dummies Guide :)

[mak]

Hi
I think that you are getting FALSE because your formula is missing an argument (you don't "say" what you need if L12 isn't = 1
so in that case change your formula to something like that


=IF(L12=1,SUM(AT12*M12-AT12),-(AT12))

[celine]

Cheers Mak

Told you I know nowt....what I want is is L12 = anything but 1 I want it to put in minus the sum of AT12

Does that help

[celine]

No still got it wrong....If the cell L12 does not have a 1 but has something else I want it to = -AT12.. so if cell L12 has noting in then AT12 will be 0

Is this getting worse? Happy New Year

[davez]

try

=IF(L12="","",IF(L12=1,SUM(AT12*M12-AT12),-(AT12)))

[celine]

Thanks Dave

That works perfect...very much appreciated....If and when you have time...would it be possible for you to break it down for me...so that I know which part did what...I know some of it...but not all..and I really would like to know.....once again thanks very much

[mak]

the formula says
if cell L12 is blank then leave it blank-if cell L12 equals 1 then place sum...etc otherwise place - AT12

=IF(L12="",""
Means IF cell L12 = blank cell ("") then (,) leave the cell blank ("")


,IF(L12=1,SUM(AT12*M12-AT12
Means IF cell L12 1 then (,) sum etc

,-(AT12)))
Means otherwise place in L12 what cell – AT112 is

hope that make sense

also you can download from here
http://www.hoffits.com/

an excel workbook with many formulas and their explanation

[celine]

Thanks for the explanation...and the download...have had a quick look...but about as understanding as Russian but will test a few out and see what happens...as a learning curve...

One more formula that would really help me is how to order numbers ( I promise no more after this )

This is to do with Race Ratings....say I have a race of 10 runners and the ratings are

35
42
6
12
87
75
72
9
54
43

What I do at the moment is sort them from highest to lowest...add a new column and fill the order in manually 1-2-3-4-5-6-7-8-9-10....You can understand that if there are a lot of meetings this can take quite some time...but it's the only way I know how to do it
I know it can be done in excel...but don't know how.....I also know that if you delete a non runner it can recalculate the order....but that might be a bit more complicated

I have downloaded some excel 2010 help video's......so this is my New Years Resolution.....to try to get this "Old Brain" to learn a bit

Meanwhile...once again any help will be much appreciated

[Captain Sensible]

I've always thought Excel's built in help screens are probably one of the most helpful out there as they show examples. When entering any formulas try using the "Insert function" button that one with the "fx" symbol and click the help on this function link if you get stuck, excel is very good as it'll show you any output on the fly whilst entering functions so you can see any errors etc.

Not too clear what you mean by the ratings tbh, I'm guessing you've used the sort buttons before? as they can sort and also expand to include adjacent cells

[celine]

I think this maybe the way to do one race at a time....not sure if it is possible to do the whole list..

=RANK(J2,$J$2:$J$10)

Found that on the Excel link I downloaded

[davez]

Hi Celine, I cannot recommend highly enough the book Excel for Dummies to get you started. Without working your way completely through such a book you will make the task of learning the capabilities of this great program all the more difficult, time consuming & frustrating. It's worth the effort, trust me. Most community colleges etc have classes as well which can help get you on your way.

Re your ratings problem, if I understand correctly you have a set of ratings in cell J2 down in horse number order 1 to 10, so I suggest the following -

-Add the following titles in cells J1 through M1 - Rtg, Hno, Hno, Rtg

-In cell K2 down to K25 enter the numbers 1 to 25

-In cell L2 enter this formula then copy it to cells L3 to L25-

=IF(J2<>"",SUMIF(J$2:J$25,M2,K$2:K$25),"")

-In Cell M2 enter this formula then copy it to cells M3 to M25-

=IF(J2<>"",LARGE(J$2:J$25,K2),"")


This will give you the ranked order highest to lowest of the ratings in column M with the horse number in column K.

Hope that helps.

[celine]

Hi Dave

Initially I don't have the ratings ranked...I only have them ranked if I do it manually race by race

So what I have at the moment is a set of ratings that now start in M2 downwards...each set for each race EG

Course Time Horse Rating
Cheltenham 12:10 Aubusson 170
Cheltenham 12:10 Vivaldi Collonges 160
Cheltenham 12:10 Royal Regatta 157
Cheltenham 12:10 Regal Diamond 156
Cheltenham 12:10 Ballyalton 143
Cheltenham 12:10 Racing Pulse 126
Southwell (AW) 12:15 Push Me 147
Southwell (AW) 12:15 Short Shrift 141
Southwell (AW) 12:15 Royal Marskell 137
Southwell (AW) 12:15 Bethan 129
Southwell (AW) 12:15 Perivale 113

I have manually sorted these..with the sort function.....So what I want to do now is in the next column is rank them automatically...rather than have to do them manually for each race.....so it would become

Course..... ........ Time....... Horse ................ Rating....Rank
Cheltenham...... 12:10....... Aubusson............ 170.........1
Cheltenham...... 12:10....... Vivaldi Collonge... 160.........2
Cheltenham...... 12:10....... Royal Regatta.......157.........3
Cheltenham...... 12:10....... Regal Diamond......156........4
Cheltenham...... 12:10....... Ballyalton..............143........5
Cheltenham...... 12:10....... Racing Pulse..........126........6
Southwell (AW). 12:15....... Push Me................147........1
Southwell (AW). 12:15....... Short Shrift............141........2
Southwell (AW). 12:15....... Royal Marskell........137........3
Southwell (AW). 12:15....... Bethan...................129........4
Southwell (AW). 12:15....... Perivale.................113........5

And so on throughout all races....sorry about the formatting...done the best I can to make it look understandable.........Thanks

[davez]

Little bit more complicated but where theres a will theres a way -

So assuming columns M thru P are your data with Course,Time,Horse,Rating then add the following headings in Col's Q thru T -

Rank Unsorted
Horse
Rating
Rank Sorted

then copy & paste the following formulas

cell Q2 -

=IF($N2<>"",SUMPRODUCT(--($N$2:$N$1000=N2),--($P2<$P$2:$P$1000))+1,"")

Cell R2 -

=IF($N2<>"",LOOKUP(2,1/(($N$2:$N$1000=N2)*($P$2:$P$1000=S2)),$O$2:$O$1000),"")

Cell S2

=IF($N2<>"",SUMPRODUCT(($Q$2:$Q$1000=T2)*($N$2:$N$1000=N2)*$P$2:$P$1000),"")

Cell T2

=IF($N2="","",IF(N2<>N1,1,W1+1))


& copy those cells down as far as you require, the formulas cover down to row 1000

[celine]

Dave

I have recreated how you have said....but I am getting a #N/A in R2 and a #VALUE! in S2 then in T2 I am getting all as 1

The data as you say runs from columns M through P in the order Course,Time.Horse,Rating

Dave

[davez]

working fine using the data you posted above celine, you can download a copy of my file here


http://snk.to/f-c7jet6bq