Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min+Max from other Tabs
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min+Max from other Tabs
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min+Max from other Tabs
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min+Max from other Tabs
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
Can we modify any of the ribbon tabs or create new ribbon tabs? | New Users to Excel | |||
hide tabs from view then lock tabs? | Excel Discussion (Misc queries) | |||
first tab to other tabs | New Users to Excel | |||
Can i set up tabs within tabs on Excel? | Excel Worksheet Functions |