Cost Formula based on 1 of 3 entries

[ExcelDummy]

I'm having problems trying to get a formula to work that would configure various pricing per cell.

What I'm looking for is using "M", "C" or "EA". The "M" meaning 1,000, the "C" meaning 100, and the "EA" meaning 1.

Say I enter the "M" in cell B2, there will be a price entered in A2, that price is based on the 1,000, 100 or 1 quantities.

In cell C2 When the Qty is entered it will then calculate a price in D2 for the total spent.

A1, the cost
B2, either M, C or EA
C2, the quantity
D2, would be the cost based on the M, C or EA

I know somewhere in the formula would have to be the cost divided by, 1,000, 100 or 1 also. I'm just having problems getting it to work.

Any tips would be appreciated, Thanks

 

[Aladin Akyurek]

On 2002-09-19 06:58, ExcelDummy wrote:
I'm having problems trying to get a formula to work that would configure various pricing per cell.

What I'm looking for is using "M", "C" or "EA". The "M" meaning 1,000, the "C" meaning 100, and the "EA" meaning 1.

Say I enter the "M" in cell B2, there will be a price entered in A2, that price is based on the 1,000, 100 or 1 quantities.

In cell C2 When the Qty is entered it will then calculate a price in D2 for the total spent.

A1, the cost
B2, either M, C or EA
C2, the quantity
D2, would be the cost based on the M, C or EA

I know somewhere in the formula would have to be the cost divided by, 1,000, 100 or 1 also. I'm just having problems getting it to work.

Any tips would be appreciated, Thanks

(1.) Activate Insert|Name|Define.
(2.) Enter M as name in the Names in Workbook box.
(3.) Enter in the Refers to box:

1000

(4.) Click OK.

Iterate the above proc for C and EA.

In D2 enter:

=A1*(C2/B2)

 

[DonkeyOte]

D1 =

IF(B1="M",(A1*1000)*C1,IF(B1="C",(A1*100)*C1,(A1*B1)*C1))

I have a suspicion that you can calculate using roman numerals too - so if you were to change the EA to I then you may be able to do something along those lines.