| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF + IF?
Is there a way to combine the IF Statement. I will be using a similar formula
for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))). |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
see if_worksheet_function in help
|
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
How about
=IF(A6="Cons",IF(B6=0.9,5,IF(B6=0.85,4,IF(B6=0. 8,3,IF(B6=0.7,2,IF(B6<0.7,1,""))))),IF(A6="Prem", IF(B6=0.91,5,IF(B6=0.86,4,IF(B6=0.83,3,IF(B6=0 .75,2,IF(B6<0.75,1,""))))),"")) "RoadKill" wrote: Is there a way to combine the IF Statement. I will be using a similar formula for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))). |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
I guess I was just missing the "")) at the end.
Thanks "JLatham" wrote: How about =IF(A6="Cons",IF(B6=0.9,5,IF(B6=0.85,4,IF(B6=0. 8,3,IF(B6=0.7,2,IF(B6<0.7,1,""))))),IF(A6="Prem", IF(B6=0.91,5,IF(B6=0.86,4,IF(B6=0.83,3,IF(B6=0 .75,2,IF(B6<0.75,1,""))))),"")) "RoadKill" wrote: Is there a way to combine the IF Statement. I will be using a similar formula for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
Actually, you do have 2 redundant IFs, because all values of B6 are
exhausted so you dont need the condition IF(B6<0.7,1,"") under A6="Cons" as this will never return "", and similarly for IF(B6<0.75,1,"") if A6="Prem". Hope this helps. Pete On Aug 29, 12:12*am, RoadKill wrote: I guess I was just missing the "")) at the end. Thanks "JLatham" wrote: How about =IF(A6="Cons",IF(B6=0.9,5,IF(B6=0.85,4,IF(B6=0. 8,3,IF(B6=0.7,2,IF(B6<0.*7,1,""))))),IF(A6="Prem" ,IF(B6=0.91,5,IF(B6=0.86,4,IF(B6=0.83,3,IF(B6= 0*.75,2,IF(B6<0.75,1,""))))),"")) "RoadKill" wrote: Is there a way to combine the IF Statement. I will be using a similar formula for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))).- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
Hi
Try =IF(A6="Cons",1+(B6=0.7)+(B6=0.8)+(B6=0.85)+(B6 =0.9), IF(A6="Prem",1+(B6=0.75)+(B6=0.83)+(B6=0.86)+(B 6=0.91),"")) -- Regards Roger Govier "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... How about =IF(A6="Cons",IF(B6=0.9,5,IF(B6=0.85,4,IF(B6=0. 8,3,IF(B6=0.7,2,IF(B6<0.7,1,""))))),IF(A6="Prem", IF(B6=0.91,5,IF(B6=0.86,4,IF(B6=0.83,3,IF(B6=0 .75,2,IF(B6<0.75,1,""))))),"")) "RoadKill" wrote: Is there a way to combine the IF Statement. I will be using a similar formula for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))). |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
Just another version of what you've already posted
=1+SUM((A6="Cons")*(B6={0.7,0.8,0.85,0.9}),(A6="P rem")*(B6={0.75,0.83,0.86,0.91})) "Roger Govier" wrote: Hi Try =IF(A6="Cons",1+(B6=0.7)+(B6=0.8)+(B6=0.85)+(B6 =0.9), IF(A6="Prem",1+(B6=0.75)+(B6=0.83)+(B6=0.86)+(B 6=0.91),"")) -- Regards Roger Govier "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... How about =IF(A6="Cons",IF(B6=0.9,5,IF(B6=0.85,4,IF(B6=0. 8,3,IF(B6=0.7,2,IF(B6<0.7,1,""))))),IF(A6="Prem", IF(B6=0.91,5,IF(B6=0.86,4,IF(B6=0.83,3,IF(B6=0 .75,2,IF(B6<0.75,1,""))))),"")) "RoadKill" wrote: Is there a way to combine the IF Statement. I will be using a similar formula for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))). |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF + IF?
Put this in C6:
=IF(A6="Cons",IF(B6=90%,5,IF(B6=85%,4,IF(B6=80% ,3,IF(B6=70%, 2,1)))),IF(A6="Prem",IF(B6=91%,5,IF(B6=86%,4,IF( B6=83%,3,IF(B6=75%, 2,1)))),"")) I think it does what you want. Hope this helps. Pete On Aug 28, 11:52*pm, RoadKill wrote: Is there a way to combine the IF Statement. I will be using a similar formula for six separate metrics so would be great if I didn't need to build a table and do index matching. Basically I want it to do this in my C6 cell: IF A6="Cons" then =IF(B6=90%, 5, IF(B6=85%,4, IF(B6=80%,3, IF(B6=70%,2, IF(B6<70%,1,""))))) and IF A6="Prem" then =IF(B6=91%, 5, IF(B6=86%,4, IF(B6=83%,3, IF(B6=75%,2, IF(B6<75%,1,""))))). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
