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
