Difficulty displaying text

[Guest]

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

 

[Guest]

.. 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>)

---

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

 

[Guest]

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

.. 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>)

---

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

 

[Max]

welcome, Tanya. Glad it helped.