Forum Discussion
AND function for each column in a spilled range
So currently I have a range of numbers B1:O6 (this range will eventually get larger), to which I am comparing to A1:A6 (this too will get larger). i.e. Each cell in row 1 is compared to A1, Each cell in row 2 is compared to A2, etc.
To make this comparison I have a Spill Cell (B8) with the formula =IF(A1:A6<B1:O6,"TRUE","FALSE") that outputs a series of TRUE and FALSE as to whether a cell in the range is larger than the cell in the A column with the same row number.
Currently I'm using =AND(EXACT(B8:B13,"TRUE")) and then dragging this along horizontally to show whether the referenced TRUE/FALSE column is all TRUE or not, this outputs the results I wish to get, but I wish to have just a single cell with a spill formula that does the same job. Is there a way to do this?
My end goal is to just have one cell with some sort of russian nesting doll type of thing going on where it just spits out a single TRUE/FALSE as to whether or not there is a column in the range that is greater than A1:A6 for every single row. So I was planning on taking whatever AND spill function I could figure out and slap an OR function onto it. But I can't even figure out the AND part.
SStandrin I think this is what you were asking for... hope it helps.
I first did the formulas one at a time , then in the bottom I did all of them inside one formula with let.
To make it dynamic I had to transform the data into tables.
5 Replies
- that is great! I still don´t have lambda in excel... looks really good and simple formula
SStandrin I think this is what you were asking for... hope it helps.
I first did the formulas one at a time , then in the bottom I did all of them inside one formula with let.
To make it dynamic I had to transform the data into tables.
- alannavarro Thank you very much, this is exactly what I needed.
