If formula

[Ntisch]

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
*20%, IF number between 8 & 10, then 1 * 30%


thanks, Ni

 

[Tom Ogilvy]

=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))))

 

[Sandy Mann]

Just for variety

=1*(IF(A1>7,0.1)+IF(A1>3,0.1)+IF(A1>0,0.1))*(A1<11)

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Ntisch]

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

 

[Trevor Shuttleworth]

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

 

[Tom Ogilvy]

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).

 

[Ntisch]

Hi Trevor,

thanks for your suggestion.

I am using your formula;

)))

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

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

Puzzled?

thank

 

[Trevor Shuttleworth]

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]

Thanks for your help Tom and Trevor.

The second formula works.

Interestingly the first formula -

)))

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

regards, Nik

 

[Sandy Mann]

Interestingly the first formula -



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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk