Ping: Excel People

[Vince]

I really should be able to figure this out on my own, but I'm tired.

I'm working on a spreadsheet (duh) and am trying to figure out a way to set up a formula that can scale with an array growing in number of columns.

For example, my current formula (which could probably be much more elegant):

=(COUNTA(C4:E4) / (COUNTA(C4:E4) + (COUNTBLANK(C4:E4))))*100

Only works for data values between C4 and E4 -- as time goes on, the array will move past E4 to F4, G4, etc. Manually editing the formula isn't the end of the world (just have to do it once and copy + paste), but it's not an ideal solution. Any ideas?

Oh, in case it's helpful, the array will grow by 3 columns per week, so it's not getting ridiculously large, though it is growing steadily.

[wade moore]

When I run into this situation I just build-out the formula ahead of time. I make sure my table I'm counting is by itself and dou a (COUNTA(C4:AA4) or whatever. If the fields are blank it won't hurt you anyways.

[Vince]

That could work...how do I go about hiding columns? I want the individual columns to still be visible as we go, but then I'd have to scroll a million miles to get to my Min/Max/Avg fields.

[wade moore]

Ah..

I usually create a seperate summary tab that has all of that information. Then, if I want to see the specific data I click over - otherwise I just use my separate summary tab.

But, to hide columns you just right click on the column and choose hide.

[Barkeep49]

I agree with Wade. I generally use a separate tab in this sort of information so that I can do very large inclusive ranges.

[SteveMax58]

You can make it grow dynamically the way you've asked...but it only adds complexity to manage going forward that it doesnt sound like you are wanting.

I would say the easiest way to solve what you have is to include an F4 with no data in your formula. Then whenever you go to add your 3 columns periodically...just R-Click on column F and insert. This will maintain and update your formula automatically.

[wade moore]

That sounds WAY too simple .

FWIW, even if you do this, I would still use a separate summary tab - but that's just my preference.

[Vince]

Originally Posted by SteveMax58

You can make it grow dynamically the way you've asked...but it only adds complexity to manage going forward that it doesnt sound like you are wanting.

I would say the easiest way to solve what you have is to include an F4 with no data in your formula. Then whenever you go to add your 3 columns periodically...just R-Click on column F and insert. This will maintain and update your formula automatically.

You mean change the formula so that it includes a blank F4 already? That would skew my percentage for the formula, wouldn't it? Basically, here's what I'm trying to do --

Each row corresponds to a player. Columns C through E are values that the player provides over different occasions. Each cell in these columns has three possible outcomes -

1. An integer - the Player was present and contributed.
2. A dash - the Player was present, but was an alternate and did not contribute.
3. Nothing - the Player was not present.

The formula basically works as such -

Non-Blank Cells / (Non-Blank Cells + Blank Cells)

To be an attendance percentage. The formula will not work accurately unless there are the proper number of columns, because it does account for empty cells, and can't distinguish an absence from a simple lack of data for the given date.

Like I mentioned before, there's probably a more elegant way to do this, but I'm still pretty noobish when it comes to Excel.

And if I'm missing something retardedly easy, sorry. I've been awake since 11 AM on Monday (can't sleep lately).

[Vince]

Just thinking out loud, perhaps I should use the integer 0 as the indicator for a non-performing alternate...but that would mess up my min/max/avg functions, unless I can come up with a way to ignore zeroes.

[wade moore]

I think this would work better using countif and sumif? Rather than count and average?

[SteveMax58]

Try this out.

=(COUNTA(C4:F4) / (COUNTA(C4:F4) + (COUNTBLANK(C4:F4)-1)))*100

I really didnt look too far into what your original intention was...it makes sense you would need to alter your formula. But nothing major...just subtract 1 from your COUNTBLANK formula to compensate for the extra blank by including an extra column.

Let me know if that works for you...or if I missed something. It worked for me in a small example I made.