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


#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
3310 posts

Uber Geek

Trusted

  #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


  #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


  #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!
1011 posts

Uber Geek

Trusted
Subscriber

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

3310 posts

Uber Geek

Trusted

  #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


  #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


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

 
 
 
 


3310 posts

Uber Geek

Trusted

  #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 and LinkedIn »



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 »

OPPO Find X2 Lite brings flagship features to mid-range 5G smartphone
Posted 29-May-2020 12:52


Sony introduces the digital camera ZV-1 for content creators
Posted 27-May-2020 12:47


Samsung Announces 2020 QLED TV Range
Posted 20-May-2020 16:29


D-Link A/NZ launches AI-Powered body temperature measuring system
Posted 20-May-2020 16:22


NortonLifeLock Online Banking Protection now available for New Zealand banks
Posted 20-May-2020 16:14


SD Express delivers new gigabyte speeds for SD memory cards
Posted 20-May-2020 15:00


D-Link A/NZ launches Nuclias cloud managed network solution hosted in Australia
Posted 11-May-2020 17:53


Logitech introduces new video streaming solution for home studios
Posted 11-May-2020 17:48


Next generation Volvo cars to be powered by Luminar LiDAR technology
Posted 7-May-2020 13:56


D-Link A/NZ launches Wi-Fi Certified EasyMesh system
Posted 7-May-2020 13:51


Spark teams up with Microsoft to bring Xbox All Access to New Zealand
Posted 7-May-2020 13:01


Microsoft plans to establish its first datacenter region in New Zealand
Posted 6-May-2020 11:35


Genesis School-gen has joined forces with Mind Lab Kids
Posted 1-May-2020 12:53


Malwarebytes expands into privacy with fast, frictionless VPN
Posted 30-Apr-2020 16:06


Kordia to donate TV airtime on Channel 200 to community groups
Posted 30-Apr-2020 16:00



Geekzone Live »

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


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



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.