If or

[Scoober]

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;300000,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.

 

[JoeU2004]

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;300000,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 news:[email protected]...

 

[Scoober]

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

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;300000,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 news:[email protected]...

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;300000,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.

 

[JoeU2004]

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

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

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;300000,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 news:[email protected]...

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;300000,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.