Formula-Cell has a letter (C=100, M=1,000) and needs to divide?

J

Judy

I have a worksheet that needs to do the following:
qty x price (divided) by (c=100 or m=1000 or each=1, etc.)
The cell will have the letter in it to represent the number.
Any help would be appreciated.
 
B

bpeltzer

A little more info on how your worksheet is organized would help. I'll
assume you've got three columns of data: qty in column A, price in column B
and the letter in column C. We'll calculate a result in column D.
I'd create a separate table of two columns, the first being the letter and
the second being the corresponding denominator. So the first row of the
table, for example, is c, 100. For aesthetics this can be on another
worksheet, and I'll assume for now that it's in columns A and B of Sheet2.
Then in D2, for example, the calculation would be
=a2*b2/vlookup(c2,Sheet2!A:B,2,false). You can autofill that formula to all
relevant rows of your table.
 
D

Duane Reynolds

I use this formula in my pricing sheets I have 1 column where e c or m is
entered and prices are calculated with the following if statement

=C13*(IF(D13="e",B13,(IF(D13="c",B13/100,IF(D13="m",B13/1000,IF(D13=0,0,"err"))))))



Duane Reynolds
 
Top