Formula for Income Statement in financial statement

**Zunit** · 07-02-2012, 12:28 PM

Hi all,

This is my first post in the forum, so please bear with me.

I am currently drafting financial statements in Excel 2007. The income statement has two columns, one for current year figures and one for prior year figures. I request assistance with the "Gross Profit/Loss" line description.

I want the description to change based on whether the figures indicate a profit or loss as follows:

Capture.PNG

Description, Current year figure, Prior year figure

Gross profit, Positive number, Positive number
Gross loss, Negative number, Negative number
Gross profit/loss, Positive number, Negative number
Gross loss/profit, Negative number, Positive number

So basically I need a formula for the description field to change based on the change in the figures as above.

Thanks very much

**K m** · 07-02-2012, 12:53 PM

If I understand correctly, this is what you are looking for:
=IF(B6-ABS(B7)>0,"Positive Number","Negative Number")

I chose ABS (absolute value) for B7 in case the information was entered incorrectly

Kirk

**K m** · 07-02-2012, 12:58 PM

BTW: the formula should be entered in cell B7.

**K m** · 07-02-2012, 01:00 PM

I'm sorry, the formula in cell B7 should be:

=IF(B5-ABS(B6)>0,"Positive Number","Negative Number")

**Zunit** · 07-02-2012, 01:19 PM

Hi Kirk,

Thanks for your response. The formula I need is for the 'description' field.

The "positive figure" or 'negative figure" field will be a number. If the number is positive then there is a "profit" hence the description has to read "Gross Profit" If the figure is negative then the description has to read "Gross Profit".

Capture.PNG

Referring the picture I attached: The problem is that if Column B is a "profit" and column C is a "loss" then the description (Column A) has to read "Gross Profit/Loss" and if Column B is a "loss" and Column C is a "profit" then then the description (Column A) has to read "Gross Loss/Profit". And yet if both Column B and C is a "profit" then the description just has to read "Gross Profit" and if both columns are a "loss" then the description has to read "Gross Loss".

Hope this makes more sense.

**K m** · 07-02-2012, 01:56 PM

Perhaps this is what you are looking for in cell A4:
Conditions
A and B are positive: If (AND(B5>0,C5>0), "Gross Profit",
A is positive, B is negative: If (AND(B5>0, C5<0), "GrossProfit/Loss"
A is negative, B is negative: If (AND(B5<0, C5<0), "Gross Loss"
else: "Gross Loss/profit"
Add the correct number of left ")"

Put it together you get this formula"
=IF(AND(B5>0,C5>0),"Gross Profit",IF(AND(B5>0,C5<0),"GrossProfit/Loss",IF(AND(B5<0,C5<0),"Gross Loss","Gross Loss/profit")))

Is this what you had in mind?

Kirk