Combine another Excel Formula

cpatte7372
cpatte7372 used Ask the Experts™
on
Hello Experts,

Can someone assist me in combining the following formulas into one formula?

=IF(AND(AND(F21>C21),AND(BL21>BM21)),B21,"")

and

=IF(AND(AND(Q21<N21),AND(BN21<BO21)),B21,"")

Cheers

Carlton
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
=if(and(f21>C21,BL21>BM21,Q21<N21,BN21<BO21),b21,"")
all conditions met
OR
formula 1 conditions met or formula 2 conditions met
=if(OR(and(f21>C21,BL21>BM21),and(Q21<N21,BN21<BO21)),b21,"")

Depending on what results you want

Thomas
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
@nutsch, brilliantly done!

Author

Commented:
nutsch

I need the second formula.

Going to test it now....

In the meantime, cheers mate...

Author

Commented:
nutsch

On the face of it, it appears to work. However, it gives me the a TRUE or FALSE answer, whereby the last part of the formula should just give me the value of the cell.

For example, if cell B21 is MSFT, if =if(OR(and(f21>C21,BL21>BM21),and(Q21<N21,BN21<BO21)),b21,"") is True or False then I should get the value MSFT in the cell.

I hope that makes sense.

Cheers


Top Expert 2008

Commented:
I get the right result when I test. Check screencast


nutsch-486931.flv
Top Expert 2008

Commented:
I get the right result when I test. Check screencast

Francis OmorutoInfrastructure and Database Design Consultant

Commented:
I've just tested and it works fine for me without any changes!
Please re-check the formula you have typed in.

Author

Commented:
nutsch,

Pretty impressive screencast.

Anyway, I typed in the following formula

=IF(OR(AND(F21>C21,BL21>BM21),AND(Q21<N21,BN21<BO21)),B21,"")

Author

Commented:
I curious as to why I get a TRUE statement, rather than the value in cell B21???
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
Clear any formats from the cell containing the formula...

Author

Commented:
nutsch

I have attached my spreadsheet. At this stage, I have only applied your formula to cell BU21.

You'll see what I mean.

Cheers
EE-IB-Brokersv5.xlsm
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
Very strange!
If I type the formula into BY21 and then copy it back to BU21, it works.

Curious!
No, I haven't figured out why it is so yet.

Author

Commented:
Ahhhhhhhhh,  so I wasn't going crazy :-)

Its really weird how it worked for you and nutsch.....

Author

Commented:

Anyway, I hope you continue to check it out for me, I've really must get some sleep zzzzzz

I'll be up for about another 20 mins if you have any questions.

Cheers
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
So, just try the formula in any other blank cell that you haven't touched before and see if you get the right result.
When you do, copy the formula via the formula bar, rather than just pasting it on the cell.

Time to catch forty winks!
You have entered the formula twice. Just delete the duplicate.
But you shouldn't be giving points for this. The formula was all Thomas's
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
Thomas' formula should get all the points. Was there a different suggestion?

Author

Commented:
Will someone please promote this guy, freakin genius!

Cheers man

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial