Excel won"t display currency format?

Stephenosn

Board Regular
Joined
Jun 2, 2015
Messages
52
I'm using the formula bellow for a cell that I have formatted to currency but will not show as such. I'm using excel 2010. Does anyone know how I can get excel to recognize this as currency?

Thanks in advance.

=IF(D3="Loader",E3*135,) &IF(D3="mini",E3*90,) &IF(D3=350,E3*185,) &IF(D3=190,E3*145,) &IF(D3=160,E3*135,) &IF(D3=150,E3*135,) &IF(D3="Excavator With Large Breaker",E3*335,) &IF(D3="Excavator With Small Breaker",E3*240,) &IF(D3="Five Axle Dump",E3*98,) &IF(D3="Four Axle Dump",E3*95,) &IF(D3="Dump With Pup",E3*110,) &IF(D3="Water Truck",E3*85,) &IF(D3="Truck",E3*60,) &IF(D3="Truck With Trailer",E3*65,) &IF(D3="Dump With Trailer",E3*98,) &IF(D3="Truck With Dump Trailer",E3*65,) &IF(D3="Skid Steer",E3*70,) &IF(D3="Big Plate",E3*125,) &IF(D3="Small Plate",E3*50,) &IF(D3="Jumping Jack",E3*50,) &IF(D3="D6",E3*150,) &IF(D3="Backhoe",E3*95,) &IF(D3="Labor",E3*35,) &IF(D3="Hubber",E3*70,) &IF(D3="Water Trailer",E3*85,) &IF(D3="Water Truck",E3*85,) &IF(D3="Small Roller",E3*35+300,)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
you could try...

=TEXT(IF(D3="Loader",E3*135,) &IF(D3="mini",E3*90,) &IF(D3=350,E3*185,) &IF(D3=190,E3*145,) &IF(D3=160,E3*135,) &IF(D3=150,E3*135,) &IF(D3="Excavator With Large Breaker",E3*335,) &IF(D3="Excavator With Small Breaker",E3*240,) &IF(D3="Five Axle Dump",E3*98,) &IF(D3="Four Axle Dump",E3*95,) &IF(D3="Dump With Pup",E3*110,) &IF(D3="Water Truck",E3*85,) &IF(D3="Truck",E3*60,) &IF(D3="Truck With Trailer",E3*65,) &IF(D3="Dump With Trailer",E3*98,) &IF(D3="Truck With Dump Trailer",E3*65,) &IF(D3="Skid Steer",E3*70,) &IF(D3="Big Plate",E3*125,) &IF(D3="Small Plate",E3*50,) &IF(D3="Jumping Jack",E3*50,) &IF(D3="D6",E3*150,) &IF(D3="Backhoe",E3*95,) &IF(D3="Labor",E3*35,) &IF(D3="Hubber",E3*70,) &IF(D3="Water Trailer",E3*85,) &IF(D3="Water Truck",E3*85,) &IF(D3="Small Roller",E3*35+300,""),"$#,##0.00")

which should display the output as text in a currency format....if you need it as a number though you would need to add 0 to the end, +0, then just format it normally as currency.
 
Upvote 0
Welcome to the board.

You should build a lookup table, in say G1:H20

Excel Workbook
GH
1Loader135
2mini90
3350185
4etcetc
Sheet1




Then use
=E3*VLOOKUP(D3,G1:H20,2,FALSE)
 
Last edited:
Upvote 0
Jonmo1 I like your idea. I have the list on a separate sheet titled "Summary". Could I reference it from there?

Thanks.
 
Upvote 0
Yep, just need to add a sheet reference to the range

=E3*VLOOKUP(D3,'Summary'!G1:H20,2,FALSE)


The only hiccup I can see is the last value in your formula that does
IF(D3="Small Roller",E3*35+300,)
that +300 could be difficult. But still doable. standby
 
Upvote 0
Make sure to put just 35 in the H column where you put Small Roller in the G Column.

Then do
=E3*VLOOKUP(D3,'Summary'!G1:H20,2,FALSE)+(300*(D3="Small Roller))
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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