# Combine another Excel Formula

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® 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
Infrastructure and Database Design Consultant

Commented:
@nutsch, brilliantly done!

Commented:
nutsch

I need the second formula.

Going to test it now....

In the meantime, cheers mate...

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

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

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,"")

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

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

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

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

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

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

Commented:
You have entered the formula twice. Just delete the duplicate.

Commented:
But you shouldn't be giving points for this. The formula was all Thomas's
Infrastructure and Database Design Consultant

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

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

Cheers man

Do more with