Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## casanova218

61 posts

Master Geek

Topic # 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

3275 posts

Uber Geek

Trusted

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.

## iMapping

4 posts

Wannabe Geek

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

## iMapping

4 posts

Wannabe Geek

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

Go Hawks!
825 posts

Ultimate Geek

Trusted
Subscriber

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

3275 posts

Uber Geek

Trusted

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

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

=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

3275 posts

Uber Geek

Trusted

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.

News »

Windows no longer Microsoftâ€™s main focus
Posted 13-Mar-2018 07:47

Why phone makers are obsessed with cameras
Posted 11-Mar-2018 12:25

New Zealand Adopts International Open Data Charter
Posted 3-Mar-2018 12:48

Shipments tumble as NZ phone upgrades slow
Posted 2-Mar-2018 11:48

Oppo R11s: high-end Android, budget price
Posted 27-Feb-2018 16:27

Samsung New Zealand introducing the Galaxy S9 and S9+
Posted 26-Feb-2018 07:00

Fujifilm X beats its best with new top of the range, high-performance camera
Posted 24-Feb-2018 14:05

One million kiwis affected by cybercrime
Posted 24-Feb-2018 13:58

New Zealanders want to engage with government online and via mobile apps
Posted 24-Feb-2018 13:56

Samsung launches Samsung Max
Posted 24-Feb-2018 13:52

CPTPP text and National Interest Analysis released for public scrutiny
Posted 21-Feb-2018 19:43

Foodstuffs to trial digitised shopping trolleys
Posted 21-Feb-2018 18:27

2018: The year of zero-login, smart cars & the biometrics of things
Posted 21-Feb-2018 18:25

Intel reimagines data centre storage with new 3D NAND SSDs
Posted 16-Feb-2018 15:21

Ground-breaking business programme begins in Hamilton
Posted 16-Feb-2018 10:18

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.