# Cost Formula based on 1 of 3 entries

#### ExcelDummy

##### New Member
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

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)

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.

Replies
7
Views
471
Replies
2
Views
259
Replies
0
Views
281
Replies
2
Views
199
Replies
1
Views
456

1,207,168
Messages
6,076,907
Members
446,239
Latest member
Home Nest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back