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
3275 posts

Uber Geek
+1 received by user: 207

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!
820 posts

Ultimate Geek
+1 received by user: 41

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.

3275 posts

Uber Geek
+1 received by user: 207

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!

3275 posts

Uber Geek
+1 received by user: 207

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 »

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


Government to continue search for first Chief Technology Officer
Posted 12-Feb-2018 20:30


Time to take Appleā€™s iPad Pro seriously
Posted 12-Feb-2018 16:54


New Fujifilm X-A5 brings selfie features to mirrorless camera
Posted 9-Feb-2018 09:12


D-Link ANZ expands connected smart home with new HD Wi-Fi cameras
Posted 9-Feb-2018 09:01


Dragon Professional for Mac V6: Near perfect dictation
Posted 9-Feb-2018 08:26


OPPO announces R11s with claims to be the picture perfect smartphone
Posted 2-Feb-2018 13:28


Vocus Communications wins a place on the TaaS panel
Posted 26-Jan-2018 15:16


SwipedOn raises $1 million capital
Posted 26-Jan-2018 15:15


Slingshot offers unlimited gigabit fibre for under a ton
Posted 25-Jan-2018 13:51


Spark doubles down on wireless broadband
Posted 24-Jan-2018 15:44



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.