| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If or
Hi,
Can someone help with the following. I have two pages in my worsheet. A formula page "OOP" and a summary Page "Summary". On the formula page 'OOP' I need: In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) and add B37+B38+F35+F36+F37 + the answer of F38. (so if B35 or B36 are populated do not activeate F38, however if B35 or B36 are empty and B37 or B38 or F35 or F36 or F37 are populated active F38 and add the remaining cells to the answer of F38. On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. I hope this makes some sence and look forward to any help I can get. Thank you in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If or
"Scoober" wrote:
In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated [...] add B37+B38+F35+F36+F37 + the answer of F38. In F40: =if(count(B35:B38,F35:F38)0, sum(B35:B38,F35:F38), "") This works when B35 or B36 is non-blank because F38 will be blank, which SUM ignores; thus we get sum(B35:B38,F35:F37). And this works when B35 and B36 are blank, but at least one of B37:B38 and F35:F37 is non-blank because F38 will be non-blank (if B21 is non-blank); thus we get sum(B37:B38,F35:F38). If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) In F38: =if(or(B21="", count(B35:B36)0, count(B35:B38,F35:F37)=0), "", if(B21<=0, 0, lookup(B21,{0,2000;300000,3000;600000,4500;1000000 ,6000}))) On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. In P21: =if(oop!F40="", "", oop!F40) or =if(oop!F40="", "", formula) where "formula" is whatever you want to "print" in P21. It was not clear what you mean. ----- original message ----- in message ... Hi, Can someone help with the following. I have two pages in my worsheet. A formula page "OOP" and a summary Page "Summary". On the formula page 'OOP' I need: In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) and add B37+B38+F35+F36+F37 + the answer of F38. (so if B35 or B36 are populated do not activeate F38, however if B35 or B36 are empty and B37 or B38 or F35 or F36 or F37 are populated active F38 and add the remaining cells to the answer of F38. On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. I hope this makes some sence and look forward to any help I can get. Thank you in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If or
Thanks, the formula on the formula pages work however I get 'FALSE' when
using =IF(OOP!F40="", "", OOP!F40) and '#NAME' when using =if(oop!F40="", "", formula) on the summary sheet. Do you have any suggestions? -- Thanks in advance. Scoober "JoeU2004" wrote: "Scoober" wrote: In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated [...] add B37+B38+F35+F36+F37 + the answer of F38. In F40: =if(count(B35:B38,F35:F38)0, sum(B35:B38,F35:F38), "") This works when B35 or B36 is non-blank because F38 will be blank, which SUM ignores; thus we get sum(B35:B38,F35:F37). And this works when B35 and B36 are blank, but at least one of B37:B38 and F35:F37 is non-blank because F38 will be non-blank (if B21 is non-blank); thus we get sum(B37:B38,F35:F38). If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) In F38: =if(or(B21="", count(B35:B36)0, count(B35:B38,F35:F37)=0), "", if(B21<=0, 0, lookup(B21,{0,2000;300000,3000;600000,4500;1000000 ,6000}))) On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. In P21: =if(oop!F40="", "", oop!F40) or =if(oop!F40="", "", formula) where "formula" is whatever you want to "print" in P21. It was not clear what you mean. ----- original message ----- in message ... Hi, Can someone help with the following. I have two pages in my worsheet. A formula page "OOP" and a summary Page "Summary". On the formula page 'OOP' I need: In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) and add B37+B38+F35+F36+F37 + the answer of F38. (so if B35 or B36 are populated do not activeate F38, however if B35 or B36 are empty and B37 or B38 or F35 or F36 or F37 are populated active F38 and add the remaining cells to the answer of F38. On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. I hope this makes some sence and look forward to any help I can get. Thank you in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If or
"Scoober" wrote:
Thanks, the formula on the formula pages work however I get 'FALSE' when using =IF(OOP!F40="", "", OOP!F40) Presumably, the problem in the first case is that OOP!F40 is FALSE. Please post exactly the formula that you have in F40. Copy-and-paste it from the Formula Bar (the field next to "fx" near the toolbar) into your posting. I suspect you neglected to copy-and-paste the formula exactly as I gave it. I wrote: =if(count(B35:B38,F35:F38)0, sum(B35:B38,F35:F38), "") I presume you wrote: =if(count(B35:B38,F35:F38)0, sum(B35:B38,F35:F38)) But you have mangled the formula any number of ways. and '#NAME' when using =if(oop!F40="", "", formula) on the summary sheet. I suspect you entered the formula literally writing "formula". As I explained, "formula" was intended to represent whatever formula you intended to have in P21, if it is not simply OOP!F40. I suspect you can you ignore this suggestion. ----- original message ----- "Scoober" wrote in message ... Thanks, the formula on the formula pages work however I get 'FALSE' when using =IF(OOP!F40="", "", OOP!F40) and '#NAME' when using =if(oop!F40="", "", formula) on the summary sheet. Do you have any suggestions? -- Thanks in advance. Scoober "JoeU2004" wrote: "Scoober" wrote: In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated [...] add B37+B38+F35+F36+F37 + the answer of F38. In F40: =if(count(B35:B38,F35:F38)0, sum(B35:B38,F35:F38), "") This works when B35 or B36 is non-blank because F38 will be blank, which SUM ignores; thus we get sum(B35:B38,F35:F37). And this works when B35 and B36 are blank, but at least one of B37:B38 and F35:F37 is non-blank because F38 will be non-blank (if B21 is non-blank); thus we get sum(B37:B38,F35:F38). If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) In F38: =if(or(B21="", count(B35:B36)0, count(B35:B38,F35:F37)=0), "", if(B21<=0, 0, lookup(B21,{0,2000;300000,3000;600000,4500;1000000 ,6000}))) On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. In P21: =if(oop!F40="", "", oop!F40) or =if(oop!F40="", "", formula) where "formula" is whatever you want to "print" in P21. It was not clear what you mean. ----- original message ----- in message ... Hi, Can someone help with the following. I have two pages in my worsheet. A formula page "OOP" and a summary Page "Summary". On the formula page 'OOP' I need: In F40 If B35 or B36 is populated add B35+B36+B37+B38+F35+F36+F37, however if these cells are empty print nothing. If B35 or B36 are empty but B37 or B38 or F35 or F36 or F37 are populated make F38 calculate =IF(B21="","",IF(B21<=0,0,LOOKUP(B21,{0,2000;30000 0,3000;600000,4500;1000000,6000}))) and add B37+B38+F35+F36+F37 + the answer of F38. (so if B35 or B36 are populated do not activeate F38, however if B35 or B36 are empty and B37 or B38 or F35 or F36 or F37 are populated active F38 and add the remaining cells to the answer of F38. On the summary page I will need to print the result in P21 but leave P21 Blank if no value is in F40 on the formula sheet. I hope this makes some sence and look forward to any help I can get. Thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
