Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement
Okay, this is a strange one to me. I have 4 columns of sales numbers that
I'm looking at. If only 1 of these columns has sales (value greater than zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun". Since an if statement will only allow me to nest 3, any suggestions on a formula to use? Sample data A B C D E 100 50 0 0 0 400 50 10 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement
Try
In E1 =CHOOSE(COUNTIF(A1:D1,"0"),"Single","Double","Tri ple","Homerun") If this post helps click Yes --------------- Jacob Skaria "frykid50" wrote: Okay, this is a strange one to me. I have 4 columns of sales numbers that I'm looking at. If only 1 of these columns has sales (value greater than zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun". Since an if statement will only allow me to nest 3, any suggestions on a formula to use? Sample data A B C D E 100 50 0 0 0 400 50 10 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement
Since an if statement will only allow me to nest 3
Actually, you can nest more depending on what version of Excel you're using. However, here's a better way: =CHOOSE(COUNTIF(A1:D1,"0")+1,"","Single","Double" ,"Triple","Home Run") -- Biff Microsoft Excel MVP "frykid50" wrote in message ... Okay, this is a strange one to me. I have 4 columns of sales numbers that I'm looking at. If only 1 of these columns has sales (value greater than zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun". Since an if statement will only allow me to nest 3, any suggestions on a formula to use? Sample data A B C D E 100 50 0 0 0 400 50 10 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement
=LOOKUP(COUNTIF(a5:d5,"0"),{0,1,2,3},{"","s","d", "t"}) -- Don Guillett Microsoft MVP Excel SalesAid Software "frykid50" wrote in message ... Okay, this is a strange one to me. I have 4 columns of sales numbers that I'm looking at. If only 1 of these columns has sales (value greater than zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun". Since an if statement will only allow me to nest 3, any suggestions on a formula to use? Sample data A B C D E 100 50 0 0 0 400 50 10 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement
how come you can only nest three? excel 2003 will nest up to 7 if statements.
also, try using a COUNTIF() function. if(countif(A1:D10)=1, "single", if(countif(A1:D10)=2, "double", if(countif(A1:D10)=3, "Triple", if(countif(A1:D10)=4, "homerun", "Strikeout")))) i think its possible to do the comparison array like so. otherwise you will need another nested function to check if the values in the array are greater than 0. "frykid50" wrote: Okay, this is a strange one to me. I have 4 columns of sales numbers that I'm looking at. If only 1 of these columns has sales (value greater than zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun". Since an if statement will only allow me to nest 3, any suggestions on a formula to use? Sample data A B C D E 100 50 0 0 0 400 50 10 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement
Thank you! This works exactly like I was looking for
"Jacob Skaria" wrote: Try In E1 =CHOOSE(COUNTIF(A1:D1,"0"),"Single","Double","Tri ple","Homerun") If this post helps click Yes --------------- Jacob Skaria "frykid50" wrote: Okay, this is a strange one to me. I have 4 columns of sales numbers that I'm looking at. If only 1 of these columns has sales (value greater than zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun". Since an if statement will only allow me to nest 3, any suggestions on a formula to use? Sample data A B C D E 100 50 0 0 0 400 50 10 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |