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!!

Assumng that you are not trying to do it in the same cell, try:

=B9*IF(B93,E9,F9)

for the first one and:

=B11*IF(B1110,E11,IF(B113,F11,G11))

for the second example

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Lemony_M" wrote in message
...
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!!

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(A13,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



"Lemony_M" wrote in message
...
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!!

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

"MartinW" wrote:

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(A13,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



"Lemony_M" wrote in message
...
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!!

Thanks for your help Sandy, it was really appreciated :)

"Sandy Mann" wrote:

Assumng that you are not trying to do it in the same cell, try:

=B9*IF(B93,E9,F9)

for the first one and:

=B11*IF(B1110,E11,IF(B113,F11,G11))

for the second example

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Lemony_M" wrote in message
...
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!!

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

"MartinW" wrote:

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(A13,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



"Lemony_M" wrote in message
...
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!!

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(B1110,E11,IF(B117,G11,IF(B113,G11,H11)) )

or:

=B11*IF(B1110,3,IF(B117,5,IF(B113,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


Replace @mailinator.com with @tiscali.co.uk


"Lemony_m" wrote in message
...
How can I add another condition (4 not 3)?

"MartinW" wrote:

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(A13,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



"Lemony_M" wrote in message
...
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!!

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(A13,A1<=10),A1*C1,IF(AND(A 110,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


"Lemony_m" wrote in message
...
How can I add another condition (4 not 3)?

"MartinW" wrote:

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(A13,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



"Lemony_M" wrote in message
...
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!!

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


Replace @mailinator.com with @tiscali.co.uk


"MartinW" wrote in message
...
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(A13,A1<=10),A1*C1,IF(AND(A 110,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


"Lemony_m" wrote in message
...
How can I add another condition (4 not 3)?

"MartinW" wrote:

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(A13,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



"Lemony_M" wrote in message
...
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!!

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


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"MartinW" wrote in message
...
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(A13,A1<=10),A1*C1,IF(AND(A 110,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


"Lemony_m" wrote in message
...
How can I add another condition (4 not 3)?

"MartinW" wrote:

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(A13,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



"Lemony_M" wrote in message
...
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!!

=IF(A13,A1*K2,A1*K1)
=IF(A1<3,A1*K1,IF(AND(A1=4,A1<=9),A1*K2,IF(A110, A1*K3,"answer
undefined")))
I guess you didn't mean what you asked for in your second example, but you
can modify accordingly.
--
David Biddulph
"Lemony_M" wrote in message
...
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!!