Sep 21 2022 01:42 PM
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.
Sep 21 2022 07:35 PM
Solution@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.
Sep 21 2022 10:25 PM
@SStandrin If you have the lambda function, I believe you could also try:
Sep 22 2022 05:29 AM
Sep 22 2022 10:22 AM
Sep 22 2022 10:23 AM
Sep 21 2022 07:35 PM
Solution@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.