Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


61 posts

Master Geek


Topic # 52422 3-Dec-2009 20:42
Send private message

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)

Create new topic
3282 posts

Uber Geek
+1 received by user: 208

Trusted

  Reply # 279180 3-Dec-2009 22:25
Send private message

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.

4 posts

Wannabe Geek


  Reply # 279185 3-Dec-2009 22:42
Send private message

how about:

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

4 posts

Wannabe Geek


  Reply # 279189 3-Dec-2009 22:56
Send private message

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.

Go Hawks!
862 posts

Ultimate Geek
+1 received by user: 46

Trusted
Subscriber

  Reply # 279191 3-Dec-2009 23:00
Send private message

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.

3282 posts

Uber Geek
+1 received by user: 208

Trusted

  Reply # 279193 3-Dec-2009 23:02
Send private message

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.

91 posts

Master Geek


  Reply # 279194 3-Dec-2009 23:09
Send private message

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.



61 posts

Master Geek


  Reply # 279202 4-Dec-2009 01:31
Send private message

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!

3282 posts

Uber Geek
+1 received by user: 208

Trusted

  Reply # 279221 4-Dec-2009 09:10
Send private message

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.

Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

N4L helping TAKA Trust bridge the digital divide for Lower Hutt students
Posted 18-Jun-2018 13:08


Winners Announced for 2018 CIO Awards
Posted 18-Jun-2018 13:03


Logitech Rally sets new standard for USB-connected video conference cameras
Posted 18-Jun-2018 09:27


Russell Stanners steps down as Vodafone NZ CEO
Posted 12-Jun-2018 09:13


Intergen recognised as 2018 Microsoft Country Partner of the Year for New Zealand
Posted 12-Jun-2018 08:00


Finalists Announced For Microsoft NZ Partner Awards
Posted 6-Jun-2018 15:12


Vocus Group and Vodafone announce joint venture to accelerate fibre innovation
Posted 5-Jun-2018 10:52


Kogan.com to launch Kogan Mobile in New Zealand
Posted 4-Jun-2018 14:34


Enable doubles fibre broadband speeds for its most popular wholesale service in Christchurch
Posted 2-Jun-2018 20:07


All or Nothing: New Zealand All Blacks arrives on Amazon Prime Video
Posted 2-Jun-2018 16:21


Innovation Grant, High Tech Awards and new USA office for Kiwi tech company SwipedOn
Posted 1-Jun-2018 20:54


Commerce Commission warns Apple for misleading consumers about their rights
Posted 30-May-2018 13:15


IBM leads Call for Code to use cloud, data, AI, blockchain for natural disaster relief
Posted 25-May-2018 14:12


New FUJIFILM X-T100 aims to do better job than smartphones
Posted 24-May-2018 20:17


Stuff takes 100% ownership of Stuff Fibre
Posted 24-May-2018 19:41



Geekzone Live »

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



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.