having problems with If formula

[Duckie]

i have been working on a track and field excel program and need help
i have a formula on AG8 to BG34 i have included the file
i have in E8 14.1 seconds i need it to say 1 in AG8,
in E10 it says 15.1 seconds in AG10 it should say 1
in E12 it says 13.0 seconds in AG8 it should say 3
in E19 it say 13.0 second in AG19 it should say 2 as it equal the best time
not all cells in column E are used like all the columns up to AC

i hope this has made sense

i would appreciate some help for this as its for an track and field club for kids

[JBeaucaire]

Are you just trying to do a ranking? Or are you trying to slot all values in AI with 1 (best time) or 3 (not best time) and a 2 (tied for best time?)?

[JBeaucaire]

If it's a unique ranking you're after, this formula in AI8 copied down and over will accomplish that:
=IF(E8="","",RANK(E8,E$8:E$35)+(COUNTIF(E$8:E8,E8)-1))

If not, more detail would help.

[Duckie]

thank you JBeaucaire
it is not ranking and 1 is the slowest time and 3 is the quickest time and 2 is equal to the quickest time
the athlete gets points doing their event

[JBeaucaire]

Try this in AI8 and copy down and over:
=IF(E8="","",IF(E8=MIN(E$8:E$35),IF(MAX(AI$6:AI6)<3,3,2),IF(E8=MAX(E$8:$E$35),1,"")))

[JBeaucaire]

This little variation removes one IF statement and still works - just for fun..again in AI8 and copied around:

=IF(E8="","",IF(E8=MIN(E$8:E$35),(MAX(AI$6:AI6)<3)+2,IF(E8=MAX(E$8:$E$35),1,"")))

[Duckie]

Thanks JBeaucaire

it is working but after i put 4 or more times in the top numbers are deleted

[JBeaucaire]

It's not doing that for me, I can fill in an entire column and it gives me one 1, one 3, and as many 2s as are appropriate.

[Duckie]

maybe I am having trouble cause i am using excel 2007
i have tried all of your formulas

[JBeaucaire]

There's nothing Excel 2007ish about this issue.

Go ahead and put in your data and the formula so I can see it "not working", post it up again. I'm sure something simple is different.

[Duckie]

i have put the new formula in and have attached it

[JBeaucaire]

Working well it seems. Not a lot of data there, but what little you put in looks great.

[Duckie]

hi i have tried it on another computer with excel 2003 on a fresh sheet and it still gives me the same thing i don't know what is wrong

[JBeaucaire]

Neither do I. I don't even know what problem you're trying to solve. The sheet you posted appears to be working fine. There is one value of 1, one value of 3, and all other scores that match the high score get a 2. I don't see anything broken.

Is there even enough data here to demonstrate the problem you're having? It's not clear where/what is wrong.

[Duckie]

i will put more times and distances in the sheet for you to see i will do it later in week

[Duckie]

i have put the correct answers in, help is needed on page 2-1 manually
and this what i get with formula in, help is needed on page 2-2
cells beside dates are random used

[JBeaucaire]

Dude, this results page bears no resemblance to what you've asked for previously.

You asked for a single value of 1 for the lowest time, a single value of 3 for the highest, and any values that MATCH the highest score get a score of 2 added.

Yours is doing something completely different. I really wish you had started here...we seem to have wasted a bit of time.

Take the values in the 50m dash and explain the logic you used on those scores, maybe I'll get it this time.

[Duckie]

i hope this makes sense

at the begining of the season when they first do an event they get 1 point

next time they do that event if they better the time they get 3 points
but if they equall it they get 2 points
but if they don't do a better time they only get 1 point
its a point scoring list

[JBeaucaire]

Yep, completely different...and much simpler for some reason. Here you go. There is a different formula in row 8 than row 9+, so don't mix those up.

Also, your manual math varied in spots from the criteria, I highlighted the differences, there's some way off in column BG, too.

AG8:
=IF(C8="","",1)

AG9:
=IF(C9="","",IF(COUNT(C$8:C8)=0,1,IF(C9<MIN(C$8:C8),3,IF(C9=MIN(C$8:C8),2,1))))

[Duckie]

you are a god me a dummy
as i still have a problem with the field events as as i type in for HJ 1.50 and lower down i type in 1.53 all i get is 1 which should be 3 as the time in track is higher time to lower and field is lower to higher

[JBeaucaire]

Well, now you know what you have to do, mark it up, post it up, make sure there's enough data to completely show what you're talking about and ALL the columns in question.

[Duckie]

HI
I put the distances in i have try to change the formula where it says 1 to a 3 and 3 to a 1 but it doesn't work i have highlighted the area where the problem is as it isthe largest measurement that should get 3 at present it only gets 1

[JBeaucaire]

The formula in BA9 down and over for the FIELD section is:

=IF(W9="","",IF(COUNT(W$8:W8)=0,1,IF(W9>MAX(W$8:W8),3,IF(W9=MAX(W$8:W8),2,1))))

This is basically the opposite, using MAX functions instead of MIN.

[Duckie]

thank you very much sorry for all the mucking around it works

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[Duckie]

it won't let me mark this as SOLVED