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
3251 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!
759 posts

Ultimate Geek
+1 received by user: 24

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.

3251 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!

3251 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 »

Intel unveils the 8th Gen Intel Core Processor family for desktop
Posted 25-Sep-2017 19:45


Chow brothers plan to invest NZ$100 million in technology
Posted 24-Sep-2017 16:24


Symantec protects data everywhere with Information Centric Security
Posted 21-Sep-2017 15:33


FUJIFILM introduces X-E3 mirrorless camera with wireless connectivity
Posted 18-Sep-2017 13:53


Vodafone announces new plans with bigger data bundles
Posted 15-Sep-2017 10:51


Skinny launches phone with support for te reo Maori
Posted 14-Sep-2017 08:39


If Vodafone dropping mail worries you, you’re doing online wrong
Posted 11-Sep-2017 13:54


Vodafone New Zealand deploy live 400 gigabit system
Posted 11-Sep-2017 11:07


OPPO camera phones now available at PB Tech
Posted 11-Sep-2017 09:56


Norton Wi-Fi Privacy — Easy, flawed VPN
Posted 11-Sep-2017 09:48


Lenovo reveals new ThinkPad A Series
Posted 8-Sep-2017 14:37


Huawei passes Apple for the first time to capture the second spot globally
Posted 8-Sep-2017 10:45


Vodafone initiative enhances te reo Maori pronunciation on Google Maps
Posted 8-Sep-2017 10:40


Voyager Internet expand local internet phone services company with Conversant acquisition
Posted 6-Sep-2017 18:27


NOW Expands in to Tauranga
Posted 5-Sep-2017 18:16



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.