Min+Max from other Tabs
=IF(ISERR(MIN(Business!B6,50)/Business!B9+MAX(0,(Business!B6-50)/Business!B10,0)),0,(MIN(Business!B6,50)/Business!B9+MAX(0,(Business!B6-50)/Business!B10,0)))
I got the above to work. B6 on the Business sheet at times will display
#VALUE. When it does that I want the Totals Cell C26 to display 0.
"Sheeloo" wrote:
That means at least one cell used in the calculation contains a value which
is not a number...
Test B6, B9 and B10 on the business sheet with ISNUMBER with numbers and
when you have nothing there...
"Codeman" wrote:
The answer using your formulas still show a #VALUE in Cell C26 on the Totals
spreadsheet. When there area not any numbers on the separate Business
spreadsheet cells to calculate, I want Cell C26 on the Total spreadsheet to
put zero in Cell 26. When there are numbers in the cells on the Business
spreadsheet cells then Cell 26 on the Total spreadsheet should show the
numerical answer derived from the Business spreadsheets.
"Sheeloo" wrote:
You don't need SUM as you are alread using + between the two numbers you are
adding
Use
=IF(Business!B9<0,MIN(Business!B6,50)/Business!B9,0)+IF(Business!B10<0,MAX(0,(Business! B6-50)/Business!B10,0),0)
or
=IF(AND(Business!B9<0,Business!B10),MIN(Business! B6,50)/Business!B9+MAX(0,(Business!B6-50)/Business!B10,0),0)
depending upon your logic... first will return the MN/MAX part separately
and the second only when both have a valid value...
"Codeman" wrote:
On my Totals tab/sheet a cell has the following formula;
=IF(ISERR(Educational!B6/Educational!C9),0,Educational!B6/Educational!C9)
It produces a value of €ś0€ť when there are not any values in the cells on the
€śEducational€ť tab/sheet. The formula currently works as desired.
I have two other Occupancy use groups that have min and max parts in their
formulas that I need to have the answer be €ś0€ť on my Totals sheet when their
cell value from the other tab/sheet are zero or nothing.
I know it has to do something with =IF(ISERR in the formula in addition to
the ,0, but I have not been able to correct the formula to make it work.
Here is Cell C36s formula prior to the IF scenario.
=SUM(MIN(Business!B6,50)/Business!B9+MAX(0,(Business!B6-50)/Business!B10,0))
#VALUE is currently displayed in Cell C36 on my Total tab/sheet when there
are not any numbers in the cells on the €śBusiness€ť tab/sheet. I want Cell
C36 to read zero on the Total tab/sheet in lieu of #VALUE. Cell C36 should
read a number when there are numbers in the cells on the €śBusiness€ť tab/sheet.
Can anyone assist me on this?
Thank you
|