Formula Problem

  • Thread starter Thread starter Jay Gustafson
  • Start date Start date
J

Jay Gustafson

Hello,

I'm not exactly sure where to start with this formula. Everything I have came up with hasn't worked.

I have a column to represent each day of the month. Within those columns is different values for each day. Those fields are populated by a lookup function. So it does result in '0' for data that hasn't been entered yet. I do know it's possible to customizez the formula so that it does not return a '0', but there are times were there should be a '0'.

So with those extra 0's, it's messing up my averages. What average formula do I use, so it only calculates the days that have elapsed into the average?

Thanks,
Jay Gustafson
 
Hi Jay, here's a way:

If you have your column headers from A1 to AD1 and the
headers are actually dates, and the values are from
A2:AD2, and you which to include the 0's of past dates
into the average then (I assume that the elapsed dates are
up to date )

=AVERAGE(IF(A1:AD1<=TODAY(),A2:AD2,""))

If you wich to exclude all 0's then

=AVERAGE(IF(A2:AD2,A2:AD2,""))

Cheers
Juan

-----Original Message-----
Hello,

I'm not exactly sure where to start with this formula.
Click to expand...
Everything I have came up with hasn't worked.
I have a column to represent each day of the month.
Click to expand...
Within those columns is different values for each day.
Those fields are populated by a lookup function. So it
does result in '0' for data that hasn't been entered yet.
I do know it's possible to customizez the formula so that
it does not return a '0', but there are times were there
should be a '0'.
So with those extra 0's, it's messing up my averages.
Click to expand...
What average formula do I use, so it only calculates the
days that have elapsed into the average?
 
Back
Top