Forum Discussion
Referencing a formula
I have a cell with this formula in The sheet 'FDS VERSO' AY9 "=MIN('FDS RECTO'!P59:P82)"
Theses referenced cells ('FDS RECTO'!P59:P82) contain this formula:
for the cell P59:
=MIN(Q59:S59), The refered cells are "=another cell"
for the cell P60
=MIN(Q60:S60)
and so on. I know it can look complicated for nothing but its easier for my job.
All of those cells have the same format but the cell that contain "=MIN('FDS RECTO'!P59:P82)" does not show anything.
Is there a way to make it work? FILE INCLUDED
Thanks!
This is because some of the rows in 'FDS RECTO'!Q59:S82 contain only zeros, and hence 'FDS RECTO'!P59:P82 contains zeros.
This is in turn, because some of the rows on the CCM sheet are empty. For example, CCM!D15 is empty, and hence the formula =CCM!D15 returns 0. This is a quirk of Excel.
One workaround is to change the formula in 'FDS RECTO'!P59 to
=IF(MIN(Q59:S59)=0,"",MIN(Q59:S59))
and fill it down to P82.
4 Replies
In Excel options you can select advanced ("Erweitert" in german) and then tick the selection "show a zero in cells that have zero value".
- Yes it makes it more obvious thank you!
This is because some of the rows in 'FDS RECTO'!Q59:S82 contain only zeros, and hence 'FDS RECTO'!P59:P82 contains zeros.
This is in turn, because some of the rows on the CCM sheet are empty. For example, CCM!D15 is empty, and hence the formula =CCM!D15 returns 0. This is a quirk of Excel.
One workaround is to change the formula in 'FDS RECTO'!P59 to
=IF(MIN(Q59:S59)=0,"",MIN(Q59:S59))
and fill it down to P82.
- So simple did not think of it since my cells were blank. Thanks!
