Auto pop chart

[gluktar]

I am so so so close thanks to the help i get here.

I think this should be easy for you guys.


Basically, when the user puts in an amount i want the chart to populate. Cant quite get it right. (i think it is my nesting skilz, or lack of)

I have attached the sheet, and the trouble cells are in yellow.

Thanks as always.


Book2.xlsxBook2.xlsx

[Pete_UK]

Put these formulae in the cells stated:

B3: =IF(E3=0,"",IF(E3<250,1,""))
C3: =IF(AND(E3>249,E3<1000),1,"")
D3: =IF(E3>=1000,1, "")

then copy them down. Note that you generally do not want to put quotes around numbers, as that will turn them into text values and you won't be able to do any arithmetic on them.

Hope this helps.

Pete

[gluktar]

Hey there, that was great, thanks.


One more question though.

For the column that is for zero values, i thought that this would work:


=IF(H6=0,"1")

But it displays false if the value in H6 is not zero, and a one if it is left blank. I need it to only display a one if there is a zero in H6 and blank under all other situations. Thanks.

[Pete_UK]

You need to do this, then:

=IF(H6=0,1,"")

Again, do not put quotes around numbers, unless you want them to be treated as text values.

Hope this helps.

Pete

[gluktar]

Ahhh, i see, i missed the if false. Thanks so much.

However, it is still displaying a one when there is a blank in the cell.

[Pete_UK]

That's a quirk of Excel - if you link to a cell that is blank, then Excel treats it as if it contains zero. To overcome it, you have to test explicitly for it being blank, like this:

=IF(H6="","",IF(H6=0,1,""))

Hope this helps.

Pete