Multiple conditions to throw up a comment

[arnoldmiranda1603]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ODE[/TD]
[TD]FTE[/TD]
[TD]Temp[/TD]
[TD]Total[/TD]
[TD]ODE Change[/TD]
[TD]SR[/TD]
[TD]Variance[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]All in Sync[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]38[/TD]
[TD]40[/TD]
[TD]78[/TD]
[TD][/TD]
[TD][/TD]
[TD]-2[/TD]
[TD]Negative HC, please state reason[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]38[/TD]
[TD]40[/TD]
[TD]78[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]Negative ODE change, please state reason[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]38[/TD]
[TD]40[/TD]
[TD]78[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]All in Sync[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]42[/TD]
[TD]40[/TD]
[TD]82[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Positive HC, please state reason[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]42[/TD]
[TD]40[/TD]
[TD]82[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]Positive ODE change, please state reason[/TD]
[/TR]
</tbody>[/TABLE]

Hi,
I am trying to wrestle with a nested condition which should appear in the comments column based on the values in the columns.

Columns ODE (ODE has a formula: Value+ODE Change) and TOTAL are pre-inputted and I'm expecting team members to month on month update this file. They will enter numbers in column FTE and Temp, this adds up in the Total column.

If there is a variance it will automatically highlight in the variance column (Total - ODE + SR)

Based on the existing variance the team member can either choose to add a number in the SR column or make a change in the ODE Change column in positive or negative. Based on the outcome I wanted the different prompts to appear in the Comments section.

Reason for looking for a formula is due to the eventual data set which could go into 1000's

Appreciate any help.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H
1	ODE	FTE	Temp	Total	ODE Change	SR	Variance	Comments
2	80	40	40	80			0	All in sync
3	80	38	40	78			-2	Negative HC, please state reason
4	78	38	40	78	-2		0	Negative ODE change, please state reason
5	80	38	40	78		2	0	All in sync
6	80	42	40	82			2	Positive HC, please state reason
7	82	42	40	82	2		0	Positive ODE change, please state reason
Calendar

Cell Formulas
Range	Formula
H2	=IF(E2="",CHOOSE(SIGN(G2)+2,"Negative HC, please state reason","All in sync","Positive HC, please state reason"),CHOOSE(SIGN(E2)+2,"Negative ODE change, please state reason",,"Positive ODE change, please state reason"))

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:31.37px;" /><col style="width:26.61px;" /><col style="width:39.92px;" /><col style="width:36.12px;" /><col style="width:115.96px;" /><col style="width:20.91px;" /><col style="width:57.98px;" /><col style="width:483.8px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >ODE</td><td >FTE</td><td >Temp</td><td >Total</td><td >ODE Change</td><td >SR</td><td >Variance</td><td >Comments</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">80</td><td style="text-align:right; ">40</td><td style="text-align:right; ">40</td><td style="text-align:right; ">80</td><td > </td><td > </td><td style="text-align:right; ">0</td><td >All in sync</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">80</td><td style="text-align:right; ">38</td><td style="text-align:right; ">40</td><td style="text-align:right; ">78</td><td > </td><td > </td><td style="text-align:right; ">-2</td><td >Negative HC, please state reason</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">78</td><td style="text-align:right; ">38</td><td style="text-align:right; ">40</td><td style="text-align:right; ">78</td><td style="text-align:right; ">-2</td><td > </td><td style="text-align:right; ">0</td><td >Negative ODE change, please state reason</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">80</td><td style="text-align:right; ">38</td><td style="text-align:right; ">40</td><td style="text-align:right; ">78</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td><td >All in sync</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">80</td><td style="text-align:right; ">42</td><td style="text-align:right; ">40</td><td style="text-align:right; ">82</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >Positive HC, please state reason</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">82</td><td style="text-align:right; ">42</td><td style="text-align:right; ">40</td><td style="text-align:right; ">82</td><td style="text-align:right; ">2</td><td > </td><td style="text-align:right; ">0</td><td >Positive ODE, please state reason</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=IF(AND(E2="",G2=0), "All in sync",IF(G2<0,"Negative HC",IF(E2<0,"Negative ODE change",IF(G2>0,"Positive HC",IF(E2>0,"Positive ODE"))))  & ", please state reason")</td></tr></table></td></tr></table>

 

[arnoldmiranda1603]

Thanks DanteAmor, I thought this would be a complex formula, I liked the simplicity in what you recommended. It works beautifully.

 

[arnoldmiranda1603]

Thanks Fluff, maybe I am doing something wrong but the formula doesn't seem to give me the results.

 

[Fluff]

Not sure why it's not working for you. If you want to try & find out, I'm happy to help, but if you're happy to go with Dante's formula that's fine.

 

[DanteAmor]

Thanks DanteAmor, I thought this would be a complex formula, I liked the simplicity in what you recommended. It works beautifully.

I'm glad to help you. Thanks for the feedback.