help with formula

A

alvinearl

I need help with a formula. I need to multiply a number in a cell by a
depending on the number in the cell. If the number in cell c15 is a 3
need to multiply the number in c13 by 50%. If the number in c15 is a
I need to multiply the number in c13 by 40%. If it is 5 than 30%,
than 29%, 7 than 26%, 8 than 23%. Could someone help me with this
Thank yo
 
F

Frank Kabel

Hi
One way: try the following:
1. Setup a lookup table with these conditions (e.g. on a separate sheet
called 'lookup'):
A B
1 3 0.5
2 4 0.4
3 5 0.3
.....

2. Now use the following formula
=C13*VLOOKUP(C13,'lookup'!$A$1:$B$10,2,0)
 
A

alvinearl

I tried what you said but it did not work for me. not alot of experienc
with formulas. I have attached my file. The number in c15 can var
based on what you enter in the highlighted cells. I want the number i
c13 to be multiplied by a % based on what number is in c15. below ar
the &
If 3 50%
if 4 40%
if 5 30
if 6 29
if 7 26
if 8 23

I want the result to be in cell e19.

Thank yo

Attachment filename: payoff helper.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61577
 
R

RagDyer

Try this in E19:

=C13*LOOKUP(C15,{3,4,5,6,7,8},{50,40,30,29,26,23})%
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message I tried what you said but it did not work for me. not alot of experience
with formulas. I have attached my file. The number in c15 can vary
based on what you enter in the highlighted cells. I want the number in
c13 to be multiplied by a % based on what number is in c15. below are
the &
If 3 50%
if 4 40%
if 5 30
if 6 29
if 7 26
if 8 23

I want the result to be in cell e19.

Thank you

Attachment filename: payoff helper.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=615778
 
F

Frank Kabel

Hi
if I read your follow-up post correctly the value to check is in cell
C15. If yes, try
=C13*VLOOKUP(C15,'lookup'!$A$1:$B$10,2,0)

The lookup sheet just consists of two columns. The first is a value
which can be found in cell C15, column B stores the associated
percentages
 
F

Frank Kabel

Hi
in your cell C15 the value seems to be not stored as number. Try the
following:
- select cell C15
- goto 'format - Cells' and change the format to 'General'
- re-enter your value

now it should work
 
R

RagDyer

Don't you really think it would be a heck of a lot easier using the Lookup
formula with the *self-contained* values?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi
in your cell C15 the value seems to be not stored as number. Try the
following:
- select cell C15
- goto 'format - Cells' and change the format to 'General'
- re-enter your value

now it should work
 
R

RagDyer

Just perplexed at the OP.

Why he didn't try a one shot "Copy & Paste" suggested solution?
--


Regards,

RD
 
R

RagDyer

What exactly do you mean by "does not work"?

Are you getting a wrong answer, or no answer, or an error message?

If you're getting a #N/A error, then probably Frank is correct, and you
might not have true numbers in C15.

Just changing the format without editing in the formula bar or cell, will
not actually convert a text nuber to a true number recognizable to XL.

Try out this formula, which will convert a text number to a true number:
=C13*LOOKUP(--C15,{3,4,5,6,7,8},{50,40,30,29,26,23})%

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

message did all that and it still does not work?????
 
F

Frank Kabel

Hi
using your postedt file I just had to re-enter the '6' in cell C15 and
everything worked
 
A

alvinearl

The last formula worked. Thank you Thank you Thankyou!!! my work at th
rodeos will be so much easier now.

Barbar
 
R

RagDyeR

By "Last formula", do you mean the one with the minus signs in front of the
C15?

=C13*LOOKUP(--C15,{3,4,5,6,7,8},{50,40,30,29,26,23})%

If that's the case, that *proves* that your numbers were (are) text, and you
*didn't* follow Frank's instructions, which would have converted them.

It would have made your life a lot easier if you had listened to him!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


message The last formula worked. Thank you Thank you Thankyou!!! my work at the
rodeos will be so much easier now.

Barbara
 
Top