OT: MS Excel Formulas (Help Please)

[sirpoopsalot]

OT: MS Excel Formulas (Help Please)

I'm putting the finishing touches on a project but I'm having some issues finishing up the final statistics section. In particular, I'd like the ability to query cell color as part of a statistical breakdown.

For example, here's one of my 'more general' (and not particularly elegant) formulas:

=COUNTIF(Sheet1!E4:E157,"y")+
COUNTIF(Sheet2!E4:E195,"y")+
COUNTIF(Sheet3!E4:E117,"y")+
COUNTIF(Sheet4!B4:B83,"y")

That works well enough for providing a total count of "y" values within those ~400 cells over 4 different worksheets, but within all of those cells there are 5 different available cell colours (green, yellow, orange, red & purple) that I'd like to breakdown too.

However, there's no consistency to the colour patterns. For example, if I want only the green cells on Sheet1, I'm looking at the following cells: (e12:14, e19, e21:24, e32:33, e36, e39:40... etc.)

I could, theoretically, list them all individually - but if I ever insert an extra blank row or two it may throw off any direct references to those cells. Plus the formula would probably end up being thousands of characters long, so it's an understatement to say it would likely end up with an error or two (or 20).

So it would make much more sense if I could set up the formulas something like:

=COUNTIF(Sheet1!G4:G157 ANDIF(ThoseCells.CellColor=Green),"y") ... etc.

Anyone know how I can pull this off? It's been so long since I used Excel regularly...


... or, alternatively, any suggestions on an alternative presentation-technique (that provides the 5-part breakdown without moving or rearranging any of the cells) would be welcome too.

 

[mattinm]

Re: OT: MS Excel Formulas (Help Please)

It seems as though you'd need to create a UDF (user-defined function) in order to do this in Excel. Here's a page with some functions already created and at least an explanation of the process and code if you're concerned about downloading code. I'm not sure if you would want to use a UDF in either eventuality.

Alternatively, couldn't you just use another column in excel to define the colour. Such as having F4:F157 (or whatever the next available column happens to be) have a number from 1-5 referencing the colour used. Then you could simply use an IF based on that. I haven't used excel in a while, either, but couldn't you even hide this column?

 

[sirpoopsalot]

Re: OT: MS Excel Formulas (Help Please)

Alternatively, couldn't you just use another column in excel to define the colour. Such as having F4:F157 (or whatever the next available column happens to be) have a number from 1-5 referencing the colour used. Then you could simply use an IF based on that. I haven't used excel in a while, either, but couldn't you even hide this column?

Ah yes - adding some metadata. That might not be perfect, but it should work adequately (assuming I can relearn how to nest the forumulas as necessary). Thanks, I'll keep this in mind.

Edit: Yep, that did the trick. Thanks. The only thing left now is to tie it all together.