Complicated formula

[Guest]

I need to multiply a number by a value in a cell but if the number is greater
than 3 it needs to be muliplied by a value in a different cell if that makes
any sense at all!!
I also need to be able to do the above but with 3 different values (e.g less
than three, 4 to 9 and greater than 10) Please help as I am getting very
annoyed with myself!!

 

[MartinW]

Hi Lemony,

With your number in A1, your first multiply value in B1, your second
multiply value in C1 and your third multiply value in D1.
Put this in E1.
=IF(A1<=3,A1*B1,IF(AND(A1>3,A1<=10),A1*C1,A1*D1))

You didn't specify your exact cutoff points so you may need
to adjust the less than or equals bits.

HTH
Martin

 

[Guest]

Thank you SO much you are an absolute STAR!!!

Hi Lemony,

With your number in A1, your first multiply value in B1, your second
multiply value in C1 and your third multiply value in D1.
Put this in E1.
=IF(A1<=3,A1*B1,IF(AND(A1>3,A1<=10),A1*C1,A1*D1))

You didn't specify your exact cutoff points so you may need
to adjust the less than or equals bits.

HTH
Martin

 

[Guest]

Thanks for your help Sandy, it was really appreciated

 

[Guest]

How can I add another condition (4 not 3)?

Hi Lemony,

With your number in A1, your first multiply value in B1, your second
multiply value in C1 and your third multiply value in D1.
Put this in E1.
=IF(A1<=3,A1*B1,IF(AND(A1>3,A1<=10),A1*C1,A1*D1))

You didn't specify your exact cutoff points so you may need
to adjust the less than or equals bits.

HTH
Martin

 

[Sandy Mann]

I assume that you are using Martin's formula but just for completeness with
my formula change it to:

(sorry I forget to say to put the values that you want to multiply by in
E11:H11)

=B11*IF(B11>10,E11,IF(B11>7,G11,IF(B11>3,G11,H11)))

or:

=B11*IF(B11>10,3,IF(B11>7,5,IF(B11>3,9,11)))

Bu tit is btter/easier to put the multiplcation values in cells because they
can be changed without having to find all the individual formulas

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

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

 

[MartinW]

Hi Lemony,

Sandy's approach is more efficient and the better way to go.
Just for interest sake here is an expansion of my approach.
This is assuming your cutoff values are 3,10 +15
and your multiply values are in B1,C1,D1 and E1.

=IF(A1<=3,A1*B1,IF(AND(A1>3,A1<=10),A1*C1,IF(AND(A1>10,A1<=15),A1*D1,A1*E1)))
As you can see it quickly becomes rather lengthy and hard to follow.

Instead of nesting IF statements it is often better to use the LOOKUP or
VLOOKUP
functions.

One example using the above cutoff limits and multiplication
values of say 1.5, 2.5, 3.5 and 4.5
Then use this in any cell
=A1*LOOKUP(A1,{0,4,11,16},{"1.5","2.5","3.5","4.5"})

HTH
Martin

 

[Sandy Mann]

Martin
They are not doing any harm but your formula does not need the quotes.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

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

 

[MartinW]

Thanks Sandy,

I usually use that for returning text values, where the quotes
are needed. Motor reactions on the keyboard I guess. <g>

Regards
Martin

 

[David Biddulph]

=IF(A1>3,A1*K2,A1*K1)
=IF(A1<3,A1*K1,IF(AND(A1>=4,A1<=9),A1*K2,IF(A1>10,A1*K3,"answer
undefined")))
I guess you didn't mean what you asked for in your second example, but you
can modify accordingly.