Multiple number formats in Conditional IF statement

Hi All,

I have a problem that I hoping you guys could help with.

I am trying to create a matrix that consists of cells derived from results of an IF statement. The issue I have is that when the condition is TRUE, I would like the IF statement to return a value formatted in $ (accounting dollars) and for the FALSE condition, I would like the statement to return a custom format. The custom format is nothing but the result (now in %) appended with custom text).

D4 = $36.80
B14 = $1000
C13 = "12 Months"
D13 = "24 Months"
E13 = "36 Months"

The condition looks like this..
=IF($B14/LEFT(C$13,2)>$D$4,$D$4-$B14/LEFT(C$13,2),($B14/LEFT(C$13,2))/$D$4&"% OFF")

And my results look like this (from left to right - and this is the 1st row of the matrix)

$(46.53) $(4.87) 0.754830917874396% OFF

While the numbers are correct, in the last cell, I am unable to format. It should read as 75.48% OFF.

Would appreciate your help.

Thanks much...

Not sure exactly what you are going for as a whole, but to make the change to the percentage just change:

($B14/LEFT(C$13,2))/$D$4&"% OFF")


to

(ROUND(($B14/LEFT(C$13,2))/($D$4)*100,2)&"% OFF"))

Thank you, Rahbee. This defintely helps for a part of the problem.

Any clue as how mutiple number formats could co-exist in a single IF condition?

Regards,
-Laxman

Exxel:

This is where I seem to be confused.

Question 1: Is the format $(value.value) what you wish to have yielded for TRUE statements [ from your ex. $(46.53) ]?

Question 2: Do you desire to autofill this equation across a row, thereby making a 3 x N matrix?

Originally Posted by Rahbee

Exxel:

This is where I seem to be confused....

?

Question 3: How will you input multiple pieces of data (i.e., are all data placed in the worksheet 2x3 matrices)?

Good questions.

Answer 1 - Yes, the $ (dollar) format is yielded for a TRUE condition
Answer 2 - Yes, the goal is to autofill across a number of cell B14 choices. In this example, it is $1000But in reality there are a number of variations such as (B14:B20 = $1000,$900,$800,$700,$600...). And that will result in a Nx3 matrix, depending on the number of variables.
Answer 3 - As I build this matrix, there will be two number formats that will co-exist in these cells - one will be the $ (dollar) and the other will be a custom format.

BTW, your Round() fix worked beautifully. But now, I am curious about the fact that 'can more than one number format exist in an IF statement'? If yes, then how?

Thx.

Do you have an a copy of the worksheet? Changing the number format is easy, but I was trying to get a hold of how your data is structured, it can easily be filled across the worksheet.

Here is the equation with the number format. FYI, as you formula currently stands, it yields negative dollar values as a result of the bolded part of the equation:

=IF($B14/LEFT(C$13,2)>$D$4,CONCATENATE("$(",ROUND($D$4-$B14/LEFT(C$13,2),2),")"),(ROUND(($B14/LEFT(C$13,2))/($D$4)*100,2)&"% OFF"))