Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
in F12 want these to print
IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
If I understand what you are trying to do with this formula, try this:
=IF(E12=100%,20,IF(AND(E12<100%,E1279%),"10",IF(A ND(E12=0%,E12<50%),""))) "GinnyJ" wrote in message ... in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
You could try this:
=IF(E12="","",IF(E12<0.5,0,IF(E12<0.8,5,IF(E12<1,1 0,20)))) Basically, if E12 is blank then return a blank, otherwise if E12 is less than 50% then return 0, otherwise * if E12 is less than 80% then return 5 (my guess) ** if E12 is less than 100% then return 10, otherwise *** return 20 * we've already checked for less than 50%, so this is essentially between 50% and 79%, but you didn't specify what you wanted this to be - I've assumed 5. ** returns 10 if E1 is between 80 and 99% *** returns 20 only if E12 is greater than or equal to 100% There were some unspecified values/ranges in your formula, so hopefully you can see how this plugs the gaps. Hope this helps. Pete GinnyJ wrote: in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
=IF(E12="","",IF(E12<=0.79,0,IF(E12<=0.99,10,100)) )
"GinnyJ" wrote: in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
This gave me a "FALSE" answer in the cell instead of returning the desired
numbers, i.ei. "20" "20" or "0" "PCLIVE" wrote: If I understand what you are trying to do with this formula, try this: =IF(E12=100%,20,IF(AND(E12<100%,E1279%),"10",IF(A ND(E12=0%,E12<50%),""))) "GinnyJ" wrote in message ... in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
What is the value in E12?
"GinnyJ" wrote in message ... This gave me a "FALSE" answer in the cell instead of returning the desired numbers, i.ei. "20" "20" or "0" "PCLIVE" wrote: If I understand what you are trying to do with this formula, try this: =IF(E12=100%,20,IF(AND(E12<100%,E1279%),"10",IF(A ND(E12=0%,E12<50%),""))) "GinnyJ" wrote in message ... in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
I didn't seen in your formula that states what the value should be if the %
is between 50 and 80. The formula below assumes that: From 0% to 49% returns blank. From 50% to 79% returns 5. From 80% to 99% returns 10. 100% returns 20. =IF(E90=100%,20,IF(AND(E90<100%,E9079%),10,IF(AND (E9049%,E90<80%),5,IF(AND(E90=0%,E90<50%),"")))) "PCLIVE" wrote in message ... What is the value in E12? "GinnyJ" wrote in message ... This gave me a "FALSE" answer in the cell instead of returning the desired numbers, i.ei. "20" "20" or "0" "PCLIVE" wrote: If I understand what you are trying to do with this formula, try this: =IF(E12=100%,20,IF(AND(E12<100%,E1279%),"10",IF(A ND(E12=0%,E12<50%),""))) "GinnyJ" wrote in message ... in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
Is E12 formatted to %
Formula works for me in that case. BTW.......why put the quotes around the 10 as in "10"? Gord Dibben MS Excel MVP On Thu, 5 Oct 2006 13:36:03 -0700, GinnyJ wrote: This gave me a "FALSE" answer in the cell instead of returning the desired numbers, i.ei. "20" "20" or "0" "PCLIVE" wrote: If I understand what you are trying to do with this formula, try this: =IF(E12=100%,20,IF(AND(E12<100%,E1279%),"10",IF(A ND(E12=0%,E12<50%),""))) "GinnyJ" wrote in message ... in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
this works except for <0.8,10 because if it's greater than 80 but less than
100, I get a 10 - the E12 less than 100 return 10 makes everything 10 what I need is: if E12 is = 100 return 20 if E12 is = 99-80 return 10 if E12 is = less than 79 return 0 "Pete_UK" wrote: You could try this: =IF(E12="","",IF(E12<0.5,0,IF(E12<0.8,5,IF(E12<1,1 0,20)))) Basically, if E12 is blank then return a blank, otherwise if E12 is less than 50% then return 0, otherwise * if E12 is less than 80% then return 5 (my guess) ** if E12 is less than 100% then return 10, otherwise *** return 20 * we've already checked for less than 50%, so this is essentially between 50% and 79%, but you didn't specify what you wanted this to be - I've assumed 5. ** returns 10 if E1 is between 80 and 99% *** returns 20 only if E12 is greater than or equal to 100% There were some unspecified values/ranges in your formula, so hopefully you can see how this plugs the gaps. Hope this helps. Pete GinnyJ wrote: in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
WAHOO!! this helped me to get it to work - Thanks so much
"PCLIVE" wrote: I didn't seen in your formula that states what the value should be if the % is between 50 and 80. The formula below assumes that: From 0% to 49% returns blank. From 50% to 79% returns 5. From 80% to 99% returns 10. 100% returns 20. =IF(E90=100%,20,IF(AND(E90<100%,E9079%),10,IF(AND (E9049%,E90<80%),5,IF(AND(E90=0%,E90<50%),"")))) "PCLIVE" wrote in message ... What is the value in E12? "GinnyJ" wrote in message ... This gave me a "FALSE" answer in the cell instead of returning the desired numbers, i.ei. "20" "20" or "0" "PCLIVE" wrote: If I understand what you are trying to do with this formula, try this: =IF(E12=100%,20,IF(AND(E12<100%,E1279%),"10",IF(A ND(E12=0%,E12<50%),""))) "GinnyJ" wrote in message ... in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need a formula for this scenario
I did say that returning 5 was my guess, as you did not specify this
range. However, this should give you what you need: =IF(E12="","",IF(E12<0.8,0,IF(E12<1,10,20))) Hope this helps. Pete GinnyJ wrote: this works except for <0.8,10 because if it's greater than 80 but less than 100, I get a 10 - the E12 less than 100 return 10 makes everything 10 what I need is: if E12 is = 100 return 20 if E12 is = 99-80 return 10 if E12 is = less than 79 return 0 "Pete_UK" wrote: You could try this: =IF(E12="","",IF(E12<0.5,0,IF(E12<0.8,5,IF(E12<1,1 0,20)))) Basically, if E12 is blank then return a blank, otherwise if E12 is less than 50% then return 0, otherwise * if E12 is less than 80% then return 5 (my guess) ** if E12 is less than 100% then return 10, otherwise *** return 20 * we've already checked for less than 50%, so this is essentially between 50% and 79%, but you didn't specify what you wanted this to be - I've assumed 5. ** returns 10 if E1 is between 80 and 99% *** returns 20 only if E12 is greater than or equal to 100% There were some unspecified values/ranges in your formula, so hopefully you can see how this plugs the gaps. Hope this helps. Pete GinnyJ wrote: in F12 want these to print IF(E12=100%,"20",IF(E12=<99%,79%,"10",IF(E12=<0%, 50%,"0"))) but don't want 0's to show if blank (before any info filled in) am having problems with the greater/lesser than portion, also can't add up the totals of the F12:F15 because Excell says won't add if cells are a result of an argument - how do I get them to total |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Is it possible to have a formula for the following scenario please | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |