need a formula for this scenario

[Guest]

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

 

[PCLIVE]

If I understand what you are trying to do with this formula, try this:

=IF(E12=100%,20,IF(AND(E12<100%,E12>79%),"10",IF(AND(E12>=0%,E12<50%),"")))

 

[Pete_UK]

You could try this:

=IF(E12="","",IF(E12<0.5,0,IF(E12<0.8,5,IF(E12<1,10,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

 

[Guest]

=IF(E12="","",IF(E12<=0.79,0,IF(E12<=0.99,10,100)))

 

[Guest]

This gave me a "FALSE" answer in the cell instead of returning the desired
numbers, i.ei. "20" "20" or "0"

 

[PCLIVE]

What is the value in E12?

This gave me a "FALSE" answer in the cell instead of returning the desired
numbers, i.ei. "20" "20" or "0"

 

[PCLIVE]

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%,E90>79%),10,IF(AND(E90>49%,E90<80%),5,IF(AND(E90>=0%,E90<50%),""))))

 

[Gord Dibben]

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

 

[Guest]

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

 

[Guest]

WAHOO!! this helped me to get it to work - Thanks so much

 

[Pete_UK]

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