adding text within formula which is concatenated

[howard]

I have the folowing formula that has twxt within the formula

 =IF(B25-C25>0,"The Net Profit "&MID(B3,9,25)&" has increased by "&TEXT(B25-C25,"R#0,##0"), IF(B25-C25<0,"The Net Profit "&MID(B3,9,25)&" has decreased by "&TEXT(B25-C25,"R(#0,##0)")&" VS"))

I need to amend this to read as follows, but canot get it to work

 =IF(B25-C25>0,"The Net Profit "&MID(B3,9,25)&" has increased by "&TEXT(B25-C25,"R#0,##0") &" Vs the prior year", IF(B25-C25<0,"The Net Profit "&MID(B3,9,25)&" has decreased by "&TEXT(B25-C25,"R(#0,##0)")&" Vs the prior year"))

It should produce the folllowing for e.g.

The Net Profit YTD for 2018 has increased by R 385,255 VS the Prior year



It would be appreciated if someone could assist me

 

[jproffer]

Unless I missed it, it looks like it both cases you're not finishing the IF function. I think you're missing the "value if false" argument.

But if the top one worked, and the bottom one doesn't, then maybe not. If the only thing you changed was to add "the Prior year" to a working formula, I can't see any reason it wouldn't work. (which is exactly zero help, I know, but I still don't see the false argument in either of your formulas)

EDIT: that would be the false argument in the second IF function within each formula. The "original" IF function has the false argument, which is in fact the second IF function...which is the one I'm talking about.

Maybe you could just remove the second IF and let that be the false argument of the first IF function...??

It would only matter if you broke dead exactly even (EXACT same profit/loss as last year) in a subsequent year.

 

[howard]

Thanks for your input


have now resolved the problem

 =IF(B25-C25>0,"The Net Profit "&MID(B3,9,25)&" has increased by "&TEXT(B25-C25,"R#0,##0") &" VS the Prior Year","The Net Profit "&MID(B3,9,25)&" has decreased by "&TEXT(B25-C25,"R(#0,##0)")&" Vs the Prior year")

 

[jproffer]

I thought that would work

As long as it's noted that, like I said above, if (VERY odd chance, I know) you would have to years with the same profit or loss, it would error out as B25-C25 would be zero, which is neither greater than or less than zero......

BUT, what are the odds eh? lol

 

[jtakw]

Hi,

How about rewriting it this way, it's shorter and covers all three possibilities:

Book1
	A
1	The Net Profit is even at R0,0 Vs the prior year
Sheet67

Cell Formulas
Range	Formula
A1	="The Net Profit "&MID(B3,9,25)&IF(B25-C25>0,"has increased by ",IF(B25-C25<0,"has decreased by ","is even at "))&TEXT(B25-C25,"R#0,##0") &" Vs the prior year"

 

[howard]

Thjanks for your formula

 

[jtakw]

You're welcome.