Help me with a 100% complete formula

[halesowenmum]

Hi. I have a stacked column chart and am setting up formulas to enable CF colouration of the chart columns.

E F G H I
0 26 51 76 100
25 50 75 99 100

Beside the first row which will contribute to the chart (row 9, first cell is C9 where the user enters the % progress they believe they've achieved) I have this formula which populates the % progress figure into the relevant column, e, f, g, h or I. [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(AND(E$2<$C9,$C9<=E$3),$C9,"")[/FONT]

I've copied right and it works fab for all the columns but Col I where if I put 100 in, it doesn't return a number to the I column; it does return '100' but into Col E..

Can anyone help with how I need to adjust the formula to enable it to say '100' if the person enters 100??? Obviously it should only show if they put 100 so it's an = situation for the value of 100.

Thank you.

 

[jorismoerings]

Hi,

The reason for not showing if the person enters a 100 is the logic in the formula. It says =IF(AND(E$2<$C9,$C9<=E$3),$C9,"").
Basically saying AND(100<100,100<=100) which will never result in TRUE hence no number will come up.

These are 2 possible solutions:

Book1
	C	D	E	F	G	H	I
1			E	F	G	H	I
2			0	26	51	76	100
3			25	50	75	99	100
4
5
6
7
8
9	23		23	0	0	0	0
10	27		0	27	0	0	0
11	52		0	0	52	0	0
12	77		0	0	0	77	0
13	100		0	0	0	0	100
14
15
16
17
18	23		23
19	27			27
20	52				52
21	77					77
22	100						100
Sheet1

Cell Formulas
Range	Formula
E18	=IF(AND($C9>E$2,$C9<=E$3),$C9,"")
E9	=INDEX(FREQUENCY($C9,$E$3:$I$3),COLUMN()-4)*$C9
F18	=IF(AND($C9>E$3,$C9<=F$3),$C9,"")