Help me with a 100% complete formula

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
CDEFGHI
1EFGHI
20265176100
325507599100
4
5
6
7
8
923230000
1027027000
1152005200
1277000770
131000000100
14
15
16
17
182323 
192727
205252
217777
22100100
Sheet1
Cell Formulas
RangeFormula
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,"")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top