Hi,

hoping someone can assist with an IF formula I am trying to write.

it goes;

= IF number between 1 & 3, then 1 *10%, IF number between 4 & 7, then 1
*20%, IF number between 8 & 10, then 1 * 30%


thanks, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))

another possibility might be

=1*if(A1<1,0,if(A1<=3,.1,if(A1<=7,.2,if(A1<=10,.3, 0))))

--
Regards,
Tom Ogilvy

"Ntisch" wrote in
message ...

Hi,

hoping someone can assist with an IF formula I am trying to write.

it goes;

= IF number between 1 & 3, then 1 *10%, IF number between 4 & 7, then 1
*20%, IF number between 8 & 10, then 1 * 30%


thanks, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile:
http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

Just for variety

=1*(IF(A17,0.1)+IF(A13,0.1)+IF(A10,0.1))*(A1<11 )

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Ntisch" wrote in
message ...

Hi,

hoping someone can assist with an IF formula I am trying to write.

it goes;

= IF number between 1 & 3, then 1 *10%, IF number between 4 & 7, then 1
*20%, IF number between 8 & 10, then 1 * 30%


thanks, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile:
http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

Hi Tom - i am trying to use yr formula below but am getting a zero
result

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))

any ideas why?

thanks, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

Ntisch

Are you putting the number in cell A1 ? If so, is it in the range 1 to 10 ?
Is the value numeric ? Could the cell have been formatted as text before
you put a value in it ? Try formatting the cell as General and then
re-inputting your number. Otherwise, no, no idea.

Regards

Trevor


"Ntisch" wrote in
message ...

Hi Tom - i am trying to use yr formula below but am getting a zero
result

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))

any ideas why?

thanks, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile:
http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

It worked fine for me for integers entered in A1. A number like 3.5 would
be excluded and return zero in accordance with the criteria you posted (or
implied in the criteria you posted).

--
Regards,
Tom Ogilvy


"Ntisch" wrote in
message ...

Hi Tom - i am trying to use yr formula below but am getting a zero
result


=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))

any ideas why?

thanks, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile:
http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

Hi Trevor,

thanks for your suggestion.

I am using your formula;

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))


I am using a cell reference as the constant - the 1 (multiplied by) in
the forumla. I am putting different numbers in the cell that is used as
the constant.

I reinputted the numbers and found that the formula works using some
numbers in the cell reference, but otherwise doesn't work instead
returning a NAME? error.

Puzzled?

thanks


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

Ntisch

the formula:

=1*IF(AND(A1=1,A1<=3),0.1,IF(AND(A1=4,A1<=7),0.2 ,IF(AND(A1=8,A1<=10),0.3,0
)))

works for me

If, as Tom suggests, your value lies between the (integer) numbers in the
formula, you will get a zero result. Hence 3.5 is between 3 and 4 so does
not meet any of the criteria.

You might want to try this instead:

=1*IF(A1<=3,0.1,IF(A1<=7,0.2,IF(A1<=10,0.3,0 )))

Regards

Trevor


"Ntisch" wrote in
message ...

Hi Trevor,

thanks for your suggestion.

I am using your formula;

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))


I am using a cell reference as the constant - the 1 (multiplied by) in
the forumla. I am putting different numbers in the cell that is used as
the constant.

I reinputted the numbers and found that the formula works using some
numbers in the cell reference, but otherwise doesn't work instead
returning a NAME? error.

Puzzled?

thanks


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile:
http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

Thanks for your help Tom and Trevor.

The second formula works.

Interestingly the first formula -

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))

Failed to work even though I wasn't using integers.

regards, Nik


--
Ntisch
------------------------------------------------------------------------
Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791
View this thread: http://www.excelforum.com/showthread...hreadid=389973

"Ntisch" wrote in
message ...

Interestingly the first formula -

=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
)))

Failed to work even though I wasn't using integers.


This formula will not work for any value between, (but not including), 3 &
4, and 7 & 8. Could that explain the failure?

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk