I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

you have over lapping values. if <8 but 15 overlaps with less than 8. can
you clarify or did you not notice

"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

I tried changing to to 7 or less but I got the same result "False".

"jnu" wrote:

you have over lapping values. if <8 but 15 overlaps with less than 8. can
you clarify or did you not notice

"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

Kim" wrote in message
...

=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

Q516<49 is the wrong syntax for an if function try:

=IF(Q550,E5,IF(and(Q516,Q5<49),E5*0.75,IF(and(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0))))--HTHSandyIn Perth, the ancient capital of with @tiscali.co.uk"Kim" wrote in ...I tried changing to to 7 or less but I got the same result "False". "jnu" wrote: you have over lapping values. if <8 but 15 overlaps with less than 8.can you clarify or did you not notice "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and49. Can someone help?

I'm still getting a "false" for the range from 8 to 15. It's not an error,
it's just putting in a false.

"Sandy Mann" wrote:

Kim" wrote in message
...

=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

Q516<49 is the wrong syntax for an if function try:

=IF(Q550,E5,IF(and(Q516,Q5<49),E5*0.75,IF(and(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0))))--HTHSandyIn Perth, the ancient capital of with @tiscali.co.uk"Kim" wrote in ...I tried changing to to 7 or less but I got the same result "False". "jnu" wrote: you have over lapping values. if <8 but 15 overlaps with less than 8.can you clarify or did you not notice "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and49. Can someone help?

Sorry I did not work though you original formula. If Q5 has 8 or 15 then it
will be missed because we are testing for 8 and < 8 but not =8 and likewise
we are testing for <15 and 16 but not =15. Try:

=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q5=8<=15,E5*0 .5,IF(Q5<8,0))))

You may wish to move the "=" part to another part of the formula depending
on your requirements.
--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Kim" wrote in message
...
I'm still getting a "false" for the range from 8 to 15. It's not an
error,
it's just putting in a false.

"Sandy Mann" wrote:

Kim" wrote in message
...

=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

Q516<49 is the wrong syntax for an if function try:


=IF(Q550,E5,IF(and(Q516,Q5<49),E5*0.75,IF(and(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0))))--HTHSandyIn
Perth, the ancient capital of
with
@tiscali.co.uk"Kim" wrote in
...I tried
changing to to 7 or less but I got the same result "False". "jnu"
wrote: you have over lapping values. if <8 but 15 overlaps with less
than 8.can you clarify or did you not notice "Kim" wrote: I
am trying to create an IF formula that if a column is <16 but 49then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0. Here is the formula
that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))
It is working for all instances except numbers that are betwen 16
and49. Can someone help?

try this idea where you work from the bottom down.
=e5*if(q550,1,if(q516,.75,if(q516,.5,if(q57,?, 0))))

--
Don Guillett
SalesAid Software

"Kim" wrote in message
...
I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5)))



"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

Thanks, that worked. I guess I was trying to make it too difficult.

"jnu" wrote:

try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5)))



"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

Hi,

I guess it would be more efficient to do one of these:

=LOOKUP(Q5,{0;8;16;50},{0;0.5;0.75;1})*E5
=VLOOKUP(Q5,{0,0;8,0.5;16,0.75;50,1},2)*E5
=INDEX({0;0.5;0.75;1},MATCH(Q5,{0;8;16;50}))*E5
=CHOOSE(MATCH(Q5,{0;8;16;50}),0,0.5,0.75,1)*E5

The fixed arrays can of course be replaced by range references.

Regards,
KL


"Kim" wrote in message ...
Thanks, that worked. I guess I was trying to make it too difficult.

"jnu" wrote:

try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5)))



"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and 49.
Can someone help?

not really........

--
Don Guillett
SalesAid Software

"KL" wrote in message
...
Hi,

I guess it would be more efficient to do one of these:

=LOOKUP(Q5,{0;8;16;50},{0;0.5;0.75;1})*E5
=VLOOKUP(Q5,{0,0;8,0.5;16,0.75;50,1},2)*E5
=INDEX({0;0.5;0.75;1},MATCH(Q5,{0;8;16;50}))*E5
=CHOOSE(MATCH(Q5,{0;8;16;50}),0,0.5,0.75,1)*E5

The fixed arrays can of course be replaced by range references.

Regards,
KL


"Kim" wrote in message
...
Thanks, that worked. I guess I was trying to make it too difficult.

"jnu" wrote:

try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5)))



"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49
then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and
49.
Can someone help?

Great thanks, very argumented answer ;-)

KL


"Don Guillett" wrote in message ...
not really........

--
Don Guillett
SalesAid Software

"KL" wrote in message
...
Hi,

I guess it would be more efficient to do one of these:

=LOOKUP(Q5,{0;8;16;50},{0;0.5;0.75;1})*E5
=VLOOKUP(Q5,{0,0;8,0.5;16,0.75;50,1},2)*E5
=INDEX({0;0.5;0.75;1},MATCH(Q5,{0;8;16;50}))*E5
=CHOOSE(MATCH(Q5,{0;8;16;50}),0,0.5,0.75,1)*E5

The fixed arrays can of course be replaced by range references.

Regards,
KL


"Kim" wrote in message
...
Thanks, that worked. I guess I was trying to make it too difficult.

"jnu" wrote:

try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5)))



"Kim" wrote:

I am trying to create an IF formula that if a column is <16 but 49
then
multiple by .75 or if the number is <8 but 15 ten multiple by ..50, if
greater than 50 100%, if less than 8 = 0.

Here is the formula that I have created
=IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0))))

It is working for all instances except numbers that are betwen 16 and
49.
Can someone help?

I think you have your less than (<) and greater than () symbols mixed up (in
your comments anyways). So this is what I assume you meant...

If x is less then 8, then 0
If x is greater than 8, and less than 15, then 0.5*x
If x is greater than 16, and less than 49, then 0.75*x
if x is greater than 50, x

Your formula has a couple of errors.
First, "Q516<49" and "Q58<15" have no logical meaning in excel. I
believe both will return FALSE no matter what the value of Q5 actually is.
You can replace "Q516<49" with something like "AND(Q516,Q5<49)". So you
"could" use the following formula...
=IF(Q550,E5,IF(AND(Q516,Q5<49),E5*0.75,IF(AND(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0))))
but this really isn't very efficient, and still has another problem.

Second, you might get wrong results because the formula produces 0 if Q5
equals 49, 50, 15, 16, and 8. For ranges you typically want to use in one
part of the formula and <= in the next, like this.
=IF(Q550,E5,IF(AND(Q516,Q5<=50),E5*0.75,IF(AND(Q 58,Q5<=16),E5*0.5,IF(Q5<=8,0))))
but in your example the ranges all meet. So you really don't need the AND
functions at all. So the formula could be.
=IF(Q550,E5,IF(Q516,E5*0.75,IF(Q58,E5*0.5,0)))
notice the second statement does NOT include "Q5<=50". This is because at
this point in the formula Q5 must be less than or equal to 50, or else it
would trigger the first test of "Q550". Notice also the last statement has
been omitted if Q5 is not greater than 8, then it must be less than or equal
to 8.

You can make it slightly smaller still by extracting the E5 in front of
the IF statement.
=E5*IF(Q550,1,IF(Q516,0.75,IF(Q5=8,0.5,0)))

This formula is pretty good and efficient, but many people (like myself)
don't like nested IF's and avoid them like the plague. Logical values (TRUE
and FALSE) are converted to 1's and 0's when you try to apply a mathatical
operator to them. So, you can use the following formula (though it might be
confusing to newer users).

=E5*((Q58)/2+(Q516)/4+(Q550)/4)