How can I return an array of 'IF' function results?

[bird333]

Ok, my ultimate goal is to have an array of 'Delete' or 'Keep' values in an array. This will feed into another function. This is the formula I am using (focus on the first 'Sumproduct' function)

=IF(SUMPRODUCT(--($C31=$C$2:INDEX($C$1:$C31,N(INDEX(ROW($C$2:$C31),)))))<=COUNTIF($C$2:$C$32,-$C31)-SUMPRODUCT(--($C31=$C$2:INDEX($C$1:$C30,INDEX(ROW($C$2:$C30),)))),"Delete","Keep")

What I want is for the formula above to calculate each result for a changing range up to the 'ROW' limit. In this example the formula would calculate based on

C31=C2:C2
then
C31=C2:C3
.
.
and finally
C31=C2:C31

Initially I was having a problem with only the first INDEX reference being returned but then I found some info about using the 'N' function to get an array of answers to return but the problem there is that it returns the values in those cells but not the cell references themselves. Of course solving that might not get the final result but I think it would be a step closer.

Any advice would be appreciated.

[AliGW]

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for? Please show just ONE version in your profile.

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[Glenn Kennedy]

A non-working formula doesn't help much. Please explain IN WORDS what logic is being used at each row to determine the result as being "Keep" or "Delete"

[Crooza]

I'm not clear on what you're trying to achieve BUT to get you started I think you're attempting to get an array formula that returns the progress cumulative toatls of column C

Try this to get started

Please Login or Register  to view this content.

If you can show in your example spreadsheet what the expected results are I might be able to take this further.

[bird333]

A non-working formula doesn't help much. Please explain IN WORDS what logic is being used at each row to determine the result as being "Keep" or "Delete"

The formula is checking to see how many items from C2:C2 (and then from C2:C3, etc) are equal to C31. If that number is less than or equal to the total number of the opposite of C31 minus the number of items that match C31 from C2:C2 (again with the increasing range) however not including C31 itself then show 'Delete' otherwise show 'Keep'. Like I stated earlier this array of answers will be used in another function.

[Crooza]

Bird333 I’m still confused sorry. When you say the total are you asking for the sum or count ?

Can you provide a very simple example?

I’ll start with a simple example and you can take it from there if I’m wrong

C2, c3, c4 are {1,2,2} say and c31 is {3}
C2:c2 therefor is 1 for sum AND 1 for count
C2:c3 is 3 if we’re summing and 2 if we’re counting so the sum equals c31 - is this a trigger point you want to capture in your array?
C2:c4 is 5 if we’re summing and 3 if we’re counting so the count equals c31 so is this a trigger point you want to capture?

It seems you are matching to a negative value but that I assume will be easy to do.

I really would suggest a nice simple explanation with example of what the array looks like using just a few cells of data.

[bird333]

I am talking about a count. In your example none of the items equal 3 (the value of C31) so the number of items that matched C31 would be '0' until the range got to C2:C31 then of course it would be '1' because C31 obviously matches itself. The 'sumproduct' functions of the formula are matching to a positive C31 and the 'countif' part is matching to a negative C31.

EDIT: I have uploaded a new spreadsheet that has some notes that hopefully will make it clearer.

[bird333]

Anybody have any ideas?

[HansDouwe]

I don't really understand what you want.

Can you explain how you would do it on paper without mentioning formulas?

And also give an example of the expected end result given certain inputs.

[bird333]

I explain the logic above (post #5) and in the attached spreadsheet in the OP. Also the spreadsheet has what should be returned for every range (the '1' in column 'N' means it should return 'Delete' if that is not clear). I'm not sure what else I can say to explain it.

[HansDouwe]

I don't understand the explanation in post #5, because it immediately starts with how and what is skipped.
And I lose the thread halfway through the sentence.

In addition, English is not my mother tongue and that also works against it.

Perhaps there are others who understand the explanation.

[FlameRetired]

....
EDIT: I have uploaded a new spreadsheet that has some notes that hopefully will make it clearer.

Bird333 please don't 'back-edit'. This interrupts the flow of conversation, specifics of what are now being discussed regarding the upload and what has already been covered. If you need to upload another workbook do it in another post. In fact please do it with all back-edits that have already been responded to.

I'm sure you understand and thanks ahead of time.

[AliGW]

Try this in F3 copied down:

=IF(COUNTIF(C$3:C3,C3)<=COUNTIF(C$2:C2,-C3),"Delete","Keep")

This is a guess, as you still haven't mocked up EXACTLY what you want to see, so it's all rather cryptic.

[Crooza]

Hi Bird333. I've looked at this a few times. I still haven't seen exactly what result you want in C31 but it looks like you want an array that has a result {"Delete", "Keep", "Delete", "Delete" .........}. Or similar

To do this results in an array calculation within an array and to be honest I'm not sure I can do that. I'm not saying its not possible but you still haven't shown what you want C31 to look like so I'm guessing a bit. I've tried a few options but nothing I can come up with in a single formula will give an array calculation within another array other than something like I posted in post number 4 for subtotals with different array lengths.

[Glenn Kennedy]

Are R4 and R5 correct?

If YES... explain WHY they are correct.

If NO... what effect does this have on your desired result. And explain why.

[bird333]

Hi Bird333. I've looked at this a few times. I still haven't seen exactly what result you want in C31 but it looks like you want an array that has a result {"Delete", "Keep", "Delete", "Delete" .........}. Or similar

To do this results in an array calculation within an array and to be honest I'm not sure I can do that. I'm not saying its not possible but you still haven't shown what you want C31 to look like so I'm guessing a bit. I've tried a few options but nothing I can come up with in a single formula will give an array calculation within another array other than something like I posted in post number 4 for subtotals with different array lengths.

This is exactly what I want. I was able to use the N function to force the INDEX function to give an array of items instead of just giving the first result but it gives the values in the cells and not the cell references themselves. Like I said above I don't even know if that would solve my problem but at least that would be a step in the right direction.

[bird333]

Try this in F3 copied down:

=IF(COUNTIF(C$3:C3,C3)<=COUNTIF(C$2:C2,-C3),"Delete","Keep")

This is a guess, as you still haven't mocked up EXACTLY what you want to see, so it's all rather cryptic.

Thanks but I need the results all in one cell.

[bird333]

Are R4 and R5 correct?

If YES... explain WHY they are correct.

If NO... what effect does this have on your desired result. And explain why.

Yes, they are correct.
R4 - There is 1 match to C31 in the range of C2:C4. 1 is less than or equal to total of negative C31 (4) minus positive C31 in the range of C2:C3 (0). I.e 1 <= 4 (4-0)
R5 - There are 2 matches to C31 in the range of C2:C5. 2 is less than or equal to total of negative C31 (4) minus positive C31 in the range of C2:C4 (1). I.e 2 <= 3 (4-1)