Text in cell based on another value

[nparsons75]

Hi,

I have 3 cells.

A14 - Percentage value
A17 - Percentage Value
B16 - Text result

If the value in A17 is less than the value in A14 then I need the text "BEHIND SHCHEDULE" to display.

If the value in A17 is equal than the value in A14 then I need the text "ON SHCHEDULE" to display.

If the value in A17 is GREATER than the value in A14 then I need the text "AHEAD OF SHCHEDULE" to display.

Hope you can help and thanks in advance.

 

[JackDanIce]

In B16 try:

=IF(A17 < A14,"BEHIND",IF(A17=A14,"ON","AHEAD OF")) & "SCHEDULE"<a14,"behind",if(a17=a14,"on","ahead of"))="" &="" "="" schedule"<a14,"behind",if(a17="a14," "on","ahead="" schedule"[="" code]<="" html=""></a14,"behind",if(a17=a14,"on","ahead>

 

[nparsons75]

Hi,

Thank you, that works perfectly, I have also changed it slightly to =IF(A17>A14,"AHEAD OF SCHEDULE",IF(A17<A14,"BEHIND SCHEDULE","ON TARGET")) to hopefully show "ON TARGET" when the values are the same.

however the values in A14 and A17 are percentages and have values such as 26.9857684%

I can remove the decimals in the formatting but the formula doesn't take this into account.

The chances of have both cells with exactly the same result is very unlikely, this means "ON TARGET" will never show?

Would you have any idea of how to fix or a workaround,

Thank you very much for your help and time.

 

[JackDanIce]

I'm not clear, regardless of it being a percentage or other, x > y should always evaluate to TRUE or FALSE when you are comparing numerical values.

Using the formatting to change the visual appearance of the number in the cell does not change the value within the cell.

Can you explain with examples why the "formula doesn't take this into account"? Bear in mind difference between the value in the cell vs the way the cell displays the value

 

[nparsons75]

Hi, ok, apologies.

What I need to do is have 3 possible results in B16

Ahead of Schedule
Behind Schedule
On Target

If A17 < A14 then result Behind Schedule
If A17 > A14 then result Ahead of Schedule
If A17 = A14 then result On Target

Because the resultant values of the formula is a percentage then the values could be A17 = 26.3636% A14 = 26.5432%

Although they are both very close they will never, or very rarely both be the same.

I would like the formula to treat both results as 26%, therefore both the same, resulting in the text "ON TARGET".

Hope that makes sense, my formula may not be correct for this?

 

[JackDanIce]

You suspect correct, the formula will need adjusting. Can you clarify please, with that example, 26.3% rounds to 26% but 26.5% rounds to 27%; what is the criteria for defining when they are close/stop being close?

 

[Youngdand]

=if(roundup(a17,0) < roundup(a14,0),"behind",if(roundup(a17,0)=roundup(a14,0),"on","ahead of")) & " schedule"

 

[Youngdand]

Obviously you can tweek the precision of the decimal to your requirement.

 

[nparsons75]

I would say (as below or near)

26.1 to 26.5

26.5 to 27

 

[JackDanIce]

You could consider:

=IF(ABS(A14-A17) < 0.05, "ON", IF(A14 < A17, "BEHIND", "AHEAD OF") & " SCHEDULE"

Where 0.05 is the absolute difference in values and you can adjust for size and accuracy