|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using IF AND to filter number in a range, works with dragging, but not in an Array
I have found a way in Excel (Google Sheets) that filters the ages from 5-9 inclusive. If they fit it , it returns YES otherwise NO
=IF(AND(L1>4, L1<10), "Yes", "No") I have the ages in column L, and I drag this down to autofill and it works well. I then used an array formula, that I have used to autofill the column as data is entered =ArrayFormula( IF(ROW(L:L)=1,"Suitable?", IF(ISBLANK(L:L),"",IF(AND(L:L>4,L:L<10), "Yes", "No")))) This returns NO all the time Can someone guide me how to get this to work? Many thanks |
#2
|
||||
|
||||
What do you mean by Excel (Google Sheets) ?
"Arrayformula" is not an Excel function
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Apologies, I thought google used Excel formulas,
I trained on excel and it works on Google Sheets. |
#4
|
||||
|
||||
So what are you looking for, an XL or a GS solution?
If XL please post a sample sheet ( no pics please)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Its the AND function in an array,
to get it to work =ArrayFormula( IF(ROW(L:L)=1,"rej", IF(ISBLANK(L:L),"",IF((L:L<10)*(L:L>4), "Yes", "No")))) |
#6
|
|||
|
|||
so the next part of the question is, the formula outputs yes or no.
If the output is no, I want the words not eligible written in another column. but that column cant have a formula in it, as when I run the mail merge script, it adds the date the email sent, and if there is already text in that row will skip it and go to the next one down. So in my case I have Column N =email sent O =Eligibility P = Helper cell that will look at column O and if the answer is no, it will write not eligible in Column N |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Loading an Excel range to an array in VBS | BobBridges | Excel Programming | 1 | 12-25-2020 01:04 PM |
Filter range | marif300 | Project | 3 | 05-05-2017 02:39 PM |
Error: AGGREGATE_doesn't ignore hidden rows for particualr array range | adisco | Excel | 1 | 01-10-2017 10:59 AM |
Filter by a Month that falls within a date range | MattG1225 | Excel | 2 | 12-03-2015 07:44 AM |
Range Name in Adv Filter -> NOt working | 56_kruiser | Excel | 2 | 11-20-2012 09:58 AM |