Averaging only cells with numbers

[rbrown999]

I have a spreadsheet that is assigning a number to a cell based on a
VLOOKUP. For some cells, the number doesn't exist yet, because we
haven't arrived at that moment in time yet (some cells will be
populated in the future). In those cases, the VLOOKUP'ed cell is
displaying a "#N/A". I want to average a range of the cells that have
numbers in them. For instance:

Cell K3 = 3
Cell K4 = 4
Cell K5 = 5
Cell K6 = #N/A

Obviously, I could do an "=AVERAGE(K3:K5)" and when there's data in
K6, change the formula to include K6 (=AVERAGE(K3:K6). However I
don't want to do that. I want this spreadsheet to be usable by
someone who doesn't manage formulas. I just want her to plug in the
letters, that the VLOOKUP assigns a number to, and have the average
calculate.

TIA.

 

[Dave Peterson]

I'd change the =vlookup() formula:

=if(isna(vlookup(...)),"Future",vlookup(...))

Then the =average() will work ok.

 

[rbrown999]

I'd change the =vlookup() formula:

=if(isna(vlookup(...)),"Future",vlookup(...))

Then the =average() will work ok.










--

Dave Peterson- Hide quoted text -

- Show quoted text -

That did it, thanks!!

I do have another question though. An unintended consequence of this
(that I never expected) was that I have charted the averages and
created a trendline on that chart. Now, the trendline runs across the
entire set of data, including the "future" data which isn't populated
yet. This means that my trendline goes to zero. So, can I set up a
trendline to only look at numbers greater than zero? Or as a
workaround, can I define the set of data that the trendline will
represent? If the answer to these questions is "no", I'll just drop
the idea of a trendline. But I wanted to ask.

 

[Dave Peterson]

Yep. You can avoid the problem by using =na()'s in the cells that aren't ready.

Maybe you can use a hidden column with formulas and use that to plot your graph:

=if(isnumeric(k3),k3,na())