I have this formula which works wonderfully: [formatted as a percentage]

=SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P20:AD20),2)=0),(P20:AD20))*0.01

however, I now need to average the column this formula is in, which is fine
and ordinarily this wouldn't be a problem, however I wish the cell this
formula is placed to show as "" [text] if there are no values in P20:AD20.
My need to do this is that there could more than likely be rows where no
entries have been made, thereby showing 0% hence by changing the outcome to
text when no data has been entered in relative cells will allow me to get an
accurate average.

I have considered ISNUMBER and ISBLANK but as a loss due to the complexity
of the formula itself.

Any help would be appreciated, I cannot take credit for the above formula,
'Toppers' from this support group came up with it which I am extremely
grateful for.

Thanks
Tanya

> .. I wish the cell this formula is placed to show as "" [text]
> if there are no values in P20:AD20.

If P20:AD20 are expected to house only numbers,
think you could try a front IF, something like this:
=IF(COUNT(P20:AD20)=0,"",<your formula>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tanya" wrote:
> I have this formula which works wonderfully: [formatted as a percentage]
>
> =SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P20:AD20),2)=0),(P20:AD20))*0.01
>
> however, I now need to average the column this formula is in, which is fine
> and ordinarily this wouldn't be a problem, however I wish the cell this
> formula is placed to show as "" [text] if there are no values in P20:AD20.
> My need to do this is that there could more than likely be rows where no
> entries have been made, thereby showing 0% hence by changing the outcome to
> text when no data has been entered in relative cells will allow me to get an
> accurate average.
>
> I have considered ISNUMBER and ISBLANK but as a loss due to the complexity
> of the formula itself.
>
> Any help would be appreciated, I cannot take credit for the above formula,
> 'Toppers' from this support group came up with it which I am extremely
> grateful for.
>
> Thanks
> Tanya

Thank you Max, I appreciate your help.
You saved me hours of work. I had pondering heaps of different IF statements
and you got it straight away.
Regards
Tanya

"Max" wrote:

> > .. I wish the cell this formula is placed to show as "" [text]
> > if there are no values in P20:AD20.
>
> If P20:AD20 are expected to house only numbers,
> think you could try a front IF, something like this:
> =IF(COUNT(P20:AD20)=0,"",<your formula>)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Tanya" wrote:
> > I have this formula which works wonderfully: [formatted as a percentage]
> >
> > =SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P20:AD20),2)=0),(P20:AD20))*0.01
> >
> > however, I now need to average the column this formula is in, which is fine
> > and ordinarily this wouldn't be a problem, however I wish the cell this
> > formula is placed to show as "" [text] if there are no values in P20:AD20.
> > My need to do this is that there could more than likely be rows where no
> > entries have been made, thereby showing 0% hence by changing the outcome to
> > text when no data has been entered in relative cells will allow me to get an
> > accurate average.
> >
> > I have considered ISNUMBER and ISBLANK but as a loss due to the complexity
> > of the formula itself.
> >
> > Any help would be appreciated, I cannot take credit for the above formula,
> > 'Toppers' from this support group came up with it which I am extremely
> > grateful for.
> >
> > Thanks
> > Tanya

welcome, Tanya. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tanya" <(E-Mail Removed)> wrote in message
news:E0FBA4B2-4AA2-4152-81BC-(E-Mail Removed)...
> Thank you Max, I appreciate your help.
> You saved me hours of work. I had pondering heaps of different IF
> statements
> and you got it straight away.