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

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

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

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

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

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

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

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

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

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

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