#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Embedding an OR statement in an IF statement efficiently Chatnoir11 Excel Discussion (Misc queries) 4 February 2nd 09 09:12 PM
Can an If statement answer an If statement? M.A.Tyler Excel Discussion (Misc queries) 2 June 24th 07 04:14 AM
appending and IF statement to an existing IF statement spence Excel Worksheet Functions 1 March 1st 06 12:00 AM
If statement and Isblank statement Rodney C. Excel Worksheet Functions 0 January 18th 05 09:39 PM
Help please, IF statement/SUMIF statement Brad_A Excel Worksheet Functions 23 January 11th 05 03:24 PM


All times are GMT +1. The time now is 09:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"