if statements and counters

It's been forever since I've programmed. in excel, how do you make a cell update without it being recursive as kind of a counter? Like, as a simplified example, I want to be able to enter a value in one cell, and if it is between 1 and 2 make cell a go up one but if it is between 2 and 3 make cell b go up one and if it is between 3 and 4 make cell c go up one and so on. i know to nest if statements to get it to decide which numbers it is between but how do i make it so the counter goes up where it is supposed to once the if statements determine which category it is in?
Also, i want to know how to get an average of a range of cells but only the cells that have data in them, some of which may contain 0

Welcome to the Board.

Re: your first question...

If you don't want to use VBA you would need in this instance to enable Iteration (Max Iteration set to 1) as your formulae would be circular, eg if we assume A2 contains the value of interest, ie for sake of demo:

A1: =A1+(FLOOR(A2,1)=1)
B1: =B1+(FLOOR(A2,1)=2)
C1: =C1+(FLOOR(A2,1)=3)

So as A2 alters so A1:C1 will update... it is imperative to note however that these Iterative calcs are Volatile so whenever XL recalculates they will iterate regardless of whether or not A2 was actually altered - meaning you could end up with these values increasing unexpectedly.

For this reason a VBA Change Event is often best... ie use code to update A1:C1 as when A2 is altered... again a simple ex. for sake of demo:

Please Login or Register  to view this content.

Re: your 2nd question and Averages... let's assume you want to Average D1:D10 ignoring 0's (blanks you don't need to worry about as AVERAGE would ignore non-numerics), different approaches pending version of XL:

XL2007:
=AVERAGEIF(D1:D10,"<>0",D1:D10)

Pre XL2007 - non Array assuming all values are +ve
=SUM(D1:D10)/COUNTIF(D1:D10,">0")

Pre XL2007 - Array form
=AVERAGE(IF(D1:D10<>0,D1:D10))
commited with CTRL + SHIFT + ENTER