Multiplying Cell by a variable factor

[Kappany]

Hello all, nice to see you again

I have a calculating sheet with some variables data, where D1 is a drop down list, what I need to do is as follows:

If D1 = A, then F1=E1*1.0
and If D1 = B, then F1=E1*1.2
and If D1 = B, then F1=E1*1.3
and If D1 = B, then F1=E1*1.4

how can I do that?

Thanks in advance.

[southward]

Hi,

Give this a try:

Please Login or Register  to view this content.

Cheers

[FDibbins]

Hi,

Give this a try:

Please Login or Register  to view this content.

Cheers

1st, this can be shortened to this...
=IF(d1="","",e1*if(D1="A",1,IF(D1="B",1.2,IF(D1="C",1.3,IF(D1="D",1.4,"")))))

2nd, I would probably do this with a small table containing each variable and it's value, then use a vlookup to bring back the answer. This has the advantage of letting you add more variables and/or adjusting their value, without messing with the forula

[TMS]

One way:

Please Login or Register  to view this content.

which assumes upper case letters will be selected from the list.

[Kappany]

Thanks Southward
worked Perfectly

[Kappany]

Thank you all guys
all worked very nice

[FDibbins]

Trevor, I have not seen CODE before, looks useful. Is it available in 2010?

[TMS]

@Ford: as far as I am aware, it is available in all versions.

[TMS]

@Kappany: You're welcome. Thanks for the rep.

[southward]

Or, if you expect or plan to expand your variable data you can use a VLOOKUP with a table.

Something like this:

Please Login or Register  to view this content.

a	1
b	1.2
c	1.3
d	1.4

Just add to the table and update the formula.
Can prove to more efficient than editing individual formulas if changes need to be made.

Cheers



p.s.

@FDibbins,

Sorry, didn't see your second suggestion regarding using a table and Vlookup before posting. Just read back through the post. Good idea by the way.

[southward]

Glad I could be of some assistance

[zbor]

Or this:

Please Login or Register  to view this content.

[FDibbins]

Happy to help guys, thanks for the feedback