Formatting Cell as Number - When Cell Populated by IF Statement

[PVF1799]

Hi all - this is my 1st post here. I'm trying to write a SIMPLE ERP system for our farm to track P & L. I'm currently stuck on this IF statement - Well not so much the IF statement itself - but how to format the cell after the if statement finishes it's action.

Here is the IF Statement:
=CONCATENATE(IF(B4="Half Pig",+C4*200,""),IF(B4="Whole Pig",+C4*200,""),IF(B4="Turkey",+C4*35,""),IF(B4="Chicken Broiler",+C4*10,""),IF(B4="Potatoes",+C4*0,""),IF(B4="Processing",+E4*0,""))

The statement works perfectly - but I need the results in $'s not text. Formatting the usual way DOES NOT change the cell. It's just text.

Your help is gladly accepted with thanks.

 

[Jonmo1]

Welcome to the board.

The result of a concatenate function is always TEXT, even if it appears to be numeric.
Try adding +0 to the very end.

 

[Jonmo1]

The whole thing can be simplified with a lookup type of function.

Create a lookup table in say D2:E7 for example
D2:D7 = Half Pgi, Whole Pig, Turkey etc..
E2:E7 = 200, 200 ,35, 10 etc..

Then use
=VLOOKUP(B4,D2:E7,2,0)*C4


Make sure you put 0's in E6 and E7 for Potatoes and Processing.

 

[PVF1799]

That is SO Totally Amazing - Thanks very much. You just made this farmer very happy.

 

[Jonmo1]

You're welcome.

 

[PVF1799]

You're welcome.

Can you tell me what function the 2,0 does after the range?

 

[Jonmo1]

The 2 tells vlookup to return a value from the 2nd column of the referenced range.
The 0 tells it to look for an Exact Match

That is actually a True or False option, but we tend to use 1 or 0 instead, 1=True, 0=False
In Vlookup, True means it does a 'closest match' type of search, False means it looks for Exact Match.