help with formula

  • Thread starter Thread starter alvinearl
  • Start date Start date
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
 
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)
 
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
 
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
 
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
 
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
 
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
 
Just perplexed at the OP.

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


Regards,

RD
 
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?????
 
Hi
using your postedt file I just had to re-enter the '6' in cell C15 and
everything worked
 
The last formula worked. Thank you Thank you Thankyou!!! my work at th
rodeos will be so much easier now.

Barbar
 
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
 
Back
Top