calculating columns

[Guest]

I'm trying to total a few columns with only having the formula in one column.
Here's what im trying to get:

A B C D
1 Qty Unit Unit Total
Cost Count
2 10 $10.00 c $1.00

I need the only formula to be in column D. It needs to take the letter in
column C which has a number reference (which in this case, c=100), then
divide the value in column B (here it would be $10.00/100=$0.10), then
multiply that value by the value in column A ($0.10*10=$1.00) and giving the
total in column D.

Thank you

 

[Frank Kabel]

Hi
assume you have in X1:Y10 a list of these number references. e.g.
X Y
c 100
d 20
....

use the following formula in D1:
=A1*(B1/VLOOKUP(C1,$X$1:$Y$10,2,0))
and copy this formula down

 

[Frank Stone]

hi,
the letter C will have to be a number or your formula is
going to get real complicated.
how many count codes do you have?

Frank

-----Original Message-----
I'm trying to total a few columns with only having the formula in one column.
Here's what im trying to get:

A B C D
1 Qty Unit Unit Total
Cost Count
2 10 $10.00 c $1.00

I need the only formula to be in column D. It needs to take the letter in
column C which has a number reference (which in this case, c=100), then
divide the value in column B (here it would be $10.00/100=$0.10), then
multiply that value by the value in column A

($0.10*10=$1.00) and giving the

 

[Guest]

Could you explain the "X1:Y10" please? I have three letter values I will be
using
e=1
c=100
m=1000

 

[Frank Kabel]

Hi
in cells X1:Y10 enter your values so for example:
cell X1: e
cell Y1: 1
cell X2: c
cell Y2: 100
cell X3: m
cell Y3: 1000

or course you could use any other cell range. You only have to change
the range reference in the formula

 

[Guest]

I will need to use a letter because my suppliers are to lazy to break down
the cost for how many I actually need. So if I need 20 of something the will
send me a price quote of say $10.00c telling me $10 for 100. This way I can
just email them this spreadsheet and hopefully make this process a little
simpler. I don't mind a complex formula because I'm just making a template
to use over and over.

 

[Guest]

I will have multiple rows of totals, some of them may be blank but the
formula is giving me a #N/A which is not allowing me total up all the rows.
What will I need to adjust to add up all the totals when there is a blank?
Or make a blank just equal zero?

thank you

 

[Frank Kabel]

Hi
try:
=A1*B1/(IF(C1<>"",VLOOKUP(C1,$X$1:$Y$10,2,0),1))

 

[Guest]

Here is what I'm entering:
=A16*(D16/(IF(E16< >" ",VLOOKUP(E16,Sheet2!$A$1:$B$4,2,0))))
An error is found and it is prompted to change to this:
=A16*(D16/(IF(E16<" ",VLOOKUP(E16,Sheet2!$A$1:$B$4,2,0))))
which still ggives me a #N/A.

 

[Frank Kabel]

Hi
don't use " " (with a space in between) but ""-. So try:
=A16*D16/(IF(E16<>"",VLOOKUP(E16,Sheet2!$A$1:$B$4,2,0),1))

 

[Frank Stone]

hi,
Frank Kabel seems to be helping you out more than me.
Sorry i took so long to get back to you. Frank K. is much
better at formulas than i am anyway but i did mention
something about getting complicated.
regards
Frank S.

 

[Guest]

Since you only have three values, you might try a simple nested IF statement
=IF(C2="c",A2*(B2/100),IF(C2="e",A2*B2,IF(C2="m",A2*B2/1000)))