Microsoft Excel Help (If Statement)

Forums › Desktop computing › Microsoft Excel Help (If Statement)

casanova218

61 posts

Master Geek

#52422 3-Dec-2009 20:42

I need to use an IF Statement to work out which drink made the most sales in that day and which ever made the most its name must be put in cell d20 (for example)
for example: the top row is a list of drinks, and below each drink is a sales figure for a day

(c15)     (d15)     (e15) (f15)
Coke     Fanta     Lift     OJ

(c16)     (d16)     (e16) (f156
1648     2678      825     645

i tried to do this and this is what i come up with but it says #NAME?
=IF(C16>D16 or E16 or F16,C15,IF(D16>C16 or E16 or F16,D15,IF(E16>C16 or D16 or F16,E15,IF(F16>C16 or D16 or E16,F15,0))))

(=if(c16 is bigger than d16 or e16 or f16, if true then coke (c15), if false then if(d16 is bigger than.......etc)

(cell D20 needs to have the name of the drink that had the most sales by using the if statement)

bazzer

3438 posts

Uber Geek

Trusted

#279180 3-Dec-2009 22:25

What you've written doesn't really make sense. What are you hoping "C16>D16 or E16 or F16" will match for? Just compare them one at a time.

Quic Broadband

Move to New Zealand's best fibre broadband service (affiliate link). Note that to use Quic Broadband you must be comfortable with configuring your own router.

iMapping

4 posts

Wannabe Geek

#279185 3-Dec-2009 22:42

how about:

=IF(C16=MAX(C16:F16),C15,IF(D16=MAX(C16:F16),D15,IF(E16=MAX(C16:F16),E15,F15)))

iMapping

4 posts

Wannabe Geek

#279189 3-Dec-2009 22:56

If you can swap rows 15 and 16 around so the values are above the names then:

=HLOOKUP(MAX(C15:F15),C15:F16,2)

should work.

wazzageek

1093 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#279191 3-Dec-2009 23:00

Any reason that this is being done in Excel as opposed to using a database?

In any case, I don't know the full requirement (I'm assuming that your going to have a list of days)

You will need to look through at the lookup functions that are available in Excel. A quick look through what's available in Excel 2008, I needed to use the following functions:

1/ Use max to work out which value is the max
2/ Use "MATCH()" to determine the column that holds the maximum value
3/ Use "INDEX()" to determine the column heading.

These can be rolled all into a single cell if you are familiar enough with working with formulas in Excel.

In your OP you mention that you need to use an IF - not sure if my solution above is going to fit your scenario if that's an absolute requirement...

Let me know if that makes sense - I can expand on it if required.

bazzer

3438 posts

Uber Geek

Trusted

#279193 3-Dec-2009 23:02

I agree, it's a shame you have to use IF, because there are a lot of better ways to do it.

I'd go for something like =INDEX(C15:F15, 1,MATCH(MAX(C16:F16),C16:F16,0)). Does he same as above, but you don't have to mess around with the input data.

AncestralGeek

91 posts

Master Geek

Lifetime subscriber

#279194 3-Dec-2009 23:09

If you are just working with a small number of items then the following will work up to the limit of nested IF() statements for your version of Excel.

In cell (d20) use the following formula:
=IF(C16=MAX(C16:F16),C15,IF(D16=MAX(C16:F16),D15,IF(E16=MAX(C16:F16),E15,F15)))

Coke 3,000 Coke
Fanta 2,678 Fanta
Lift    4,000 Lift
OJ     1,400 OJ

Lift    4,000

A more flexible method, and this is just one of many, is to organise the data in columns rather than rows,mirror the titles to the right of the values, use the MAX() function to find the highest value and then the =VLOOKUP(I21,I16:J19,2,0) function to return the title associated with the highest value.

casanova218

61 posts

Master Geek

#279202 4-Dec-2009 01:31

iMapping: how about:

=IF(C16=MAX(C16:F16),C15,IF(D16=MAX(C16:F16),D15,IF(E16=MAX(C16:F16),E15,F15)))

this is exactly what i needed. thanks!

bazzer

3438 posts

Uber Geek

Trusted

#279221 4-Dec-2009 09:10

AncestralGeek: A more flexible method, and this is just one of many, is to organise the data in columns rather than rows,mirror the titles to the right of the values, use the MAX() function to find the highest value and then the =VLOOKUP(I21,I16:J19,2,0) function to return the title associated with the highest value.

You don't really need to rearrange the data to rows (you could use HLOOKUP or INDEX/MATCH as detailed above).

casanova218, you should tell your teacher that in this instance IF is not the way to go. What if you have 10 different drinks at some point? IF is not extensible like INDEX/MATCH would be.