Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

combine if and

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Combine IF & AND formula with a twist

Combine IF & AND formula with a twist

ResolvedVersion 2010

Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course

Combine IF & AND formula with a twist

Hi

I am trying to combine 2 formulas, 1) being a simple if 1 cell (K16) is bigger than any number within a range (L10:L15
), 2) being a search for cell contents (D36) within another range B1:B10). Both sets of criteria have to be correct to return "traded above", if either or both are wrong then just a generic false.

A twist of this I will also use separately is if K16 is within a range of numbers (not just higher as before).

I have designed the below formula, but its failing. Any help would be much appreciated!!

IF(AND(K16>L10:l15,if(ISNUMBER(SEARCH(D36,B1:B10)))),"traded above",false)

Kind regards
Adrian

RE: Combine IF & AND formula with a twist

Hi Adrian,


Thank you for the forum question.

You are close to do it already.


If you want to work with an array you must turn your formula to an array function.

To create an array formula you must press CTRL SHIFT ENTER instead of ENTER after typing the formula.

Type:

=IF(AND(K16>L10:l15,if(ISNUMBER(SEARCH(D36,B1:B10)),true,false)),"traded above",false)

Press CTRL SHIFT ENTER

In the formula bar you will see:

{=IF(AND(K16>L10:l15,if(ISNUMBER(SEARCH(D36,B1:B10)),true,false)),"traded above",false)}

Please let me know if this do the job.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Combine IF & AND formula with a twist

Hi Jens

Unfortunately this still doesn't quite work and is returning False instead of "traded above." Perhaps is my convoluted spreadsheet but I still struggle to get it work

RE: Combine IF & AND formula with a twist

Hi Adrian,


I tried to make a dummy workbook and I tested my solution and in the dummy workbook it was working fine.

If you could forward the workbook to me, I can have a look at it.

Send it to:

admin@stl-training.co.uk



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Excel tip:

Closing all your workbooks Quickly

Hold the SHIFT key down and using the mouse click on the file menu, it will now now CLOSE ALL rather than close. This closes all workbooks down but still leaves the application open.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.