In the formula bar, highlight c4:j4<""
then hit F9.
You'll see something like:
{FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE}
Now do the same thing with C2:j2<"" and hit f9
You'll see the same kind of thing:
{FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE}
Hit escape (to reset the formula).
Now hightlight (C4:J4<"")*(C2:J2<"")
and hit F9
You'll see something like:
{0,1,0,1,0,1,0,0}
The formula is doing an "AND". The 0's are when either are false, the 1's are
when both are True.
So Tom's formula kind of gets translated into:
For each element (c4, d4, e4, ...,J4 and c2, ....,j2) look to see if both are
non-blank. If they are non-blank the use the division (X4/X2--C2&4, D2&4, etc).
If either is blank (or both are blank), then use False (not explicitly included
in Tom's original formula, but there implicitly).
(And False doesn't upset the Average calculation.)
Then multiply the whole thing by 100.
Eric G wrote:
Thanks Tom,
I haven't tried it yet. I'm just wondering what the
IF((C4:J4<"")* <------ multiplication (*) sign is for?
Not really sure what the first two parts of the formula are for.
Thanks Eric
On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy"
wrote:
=AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100
Entered with Ctrl+shift+enter rather than just Enter because this is an
array formula
Should do what you want.
--
Regards,
Tom Ogilvy
Eric G wrote in message
...
I'd really appreciate getting some help with one formula I'm working
on, if possible.
Something like:
AVERAGE(C4:J4/C$2:J$2*100)
Right now I sort of have it working.
The only problem is that with values at the moment in only C4:E4
it's giving me a 'false' average.
I could restrict the formula to the cells that have values but it
would be very tedious to keep changing it each time I add a value to
F4, G4 etc.
TIA Eric
--
Dave Peterson