Arrays References with AND OR Statements

[cpadilla0024]

Hi I am having issues getting a formula to work with an array. I want to use arrays because the automation in my workbook will allow contraction/expansion automatically with making sure formulas are copied down. Attached is a basic example.

Basically my column I is not working correctly. My guess is because the AND OR statements. I am not sure what can replace this.

 

[p45cal]

A guess, in I2:

=IFERROR(IFS((($C2#="FTE")+($C2#="Key Statistic"))>0,"FTE or KeyStat",ABS($H2#)>controls!$C$17,">threshold"),"")

Couldn't work out what you wanted to appear so you'll have to change that.

 

[Monty]

To help you with the issue, could you please provide more details and specifics of the formula in column I and the purpose it is intended to serve? Without the specific formula and purpose, it's challenging to provide a precise solution. Thank you!

 

[cpadilla0024]

Really the issue is dynamic arrays with AND or OR functions. I believe the formula is right. It just will not cooperate because of the array.

What I am using that column for is a trigger for request comment from my contacts. This is happening on another sheet in my workbook. What this formula is doing is checking if they are past certain thresholds. Thresholds that could be different depending on the category.

I was able to replicate this by using a table and Power Query but I would like it to be more dynamic with less manual steps if possible.

 

[Debaser]

What was wrong with p45cal's answer?

 

[cpadilla0024]

I am about to try it.

 

[cpadilla0024]

It does not work. Here is my argument there:

1. [C2# = "FTE" OR C2# = "Key Statistic"] AND ABS(H2#)>5% then "x"
2. ABS(F2#)>250,000 AND ABS(H2#)>10% then "x"

If none of these two are true, then I need a blank. I would like to reference the array (#) so the data will expand and contract as necessary. We drop in new data monthly and it would be a pain to manual copy formulas down all the time.

 

[Monty]

It does not work. Here is my argument there:

1. [C2# = "FTE" OR C2# = "Key Statistic"] AND ABS(H2#)>5% then "x"
2. ABS(F2#)>250,000 AND ABS(H2#)>10% then "x"

If none of these two are true, then I need a blank. I would like to reference the array (#) so the data will expand and contract as necessary. We drop in new data monthly and it would be a pain to manual copy formulas down all the time.

=IF((C2="FTE" OR C2="Key Statistic") AND ABS(H2)>5%, "x", IF(ABS(F2)>250000 AND ABS(H2)>10%, "x", ""))

Just modified what P45CAL sir suggested

 

[p45cal]

Code:
=IF((C2="FTE" OR C2="Key Statistic") AND ABS(H2)>5%, "x", IF(ABS(F2)>250000 AND ABS(H2)>10%, "x", ""))

I suspect this is derived from an AI offering. Why? Because it's drivel:
1. It doesn't even adhere to Excel formula syntax
2. Doesn't address the concerns of the OP's first sentence:

having issues getting a formula to work with an array

Tweaking @Monty's non-array offering would leave something like:

=IF(AND((OR(C2="FTE", C2="Key Statistic") ), ABS(H2)>5%), "x", IF(AND(ABS(F2)>250000, ABS(H2)>10%), "x", ""))

but would need wrapping with IFERROR and copying down.

An array formula would need to be quite different; if I've got it right:

=IFERROR(IF(((C2#="FTE")+(C2#="Key Statistic"))*(ABS(H2#)>5%),"x",IF((ABS(F2#)>250000)*(ABS(H2#)>10%),"x","")),"")

or:

=IFERROR(IFS(((C2#="FTE")+(C2#="Key Statistic"))*(ABS(H2#)>5%),"x",(ABS(F2#)>250000)*(ABS(H2#)>10%),"x"),"")

@Monty , you probably need to construct your queries to AI better so that it gives a better answer, or at least try the AI offerings yourself.

 

[Monty]

I suspect this is derived from an AI offering. Why? Because it's drivel:
1. It doesn't even adhere to Excel formula syntax
2. Doesn't address the concerns of the OP's first sentence:

Tweaking @Monty's non-array offering would leave something like:

=IF(AND((OR(C2="FTE", C2="Key Statistic") ), ABS(H2)>5%), "x", IF(AND(ABS(F2)>250000, ABS(H2)>10%), "x", ""))

but would need wrapping with IFERROR and copying down.

An array formula would need to be quite different; if I've got it right:

=IFERROR(IF(((C2#="FTE")+(C2#="Key Statistic"))*(ABS(H2#)>5%),"x",IF((ABS(F2#)>250000)*(ABS(H2#)>10%),"x","")),"")

or:

=IFERROR(IFS(((C2#="FTE")+(C2#="Key Statistic"))*(ABS(H2#)>5%),"x",(ABS(F2#)>250000)*(ABS(H2#)>10%),"x"),"")

@Monty , you probably need to construct your queries to AI better so that it gives a better answer, or at least try the AI offerings yourself.

@p45cal - Thanks for the advice! I'm still learning Excel and VBA, and I've been trying to navigate through the challenges. If you have any tips on how to construct better queries for AI or specific areas I should focus on, I'd greatly appreciate it.Thanks for the support

 

[Debaser]

@p45cal - Thanks for the advice! I'm still learning Excel and VBA, and I've been trying to navigate through the challenges. If you have any tips on how to construct better queries for AI or specific areas I should focus on, I'd greatly appreciate it.Thanks for the support

What are you actually learning from copying and pasting responses out of an AI tool apparently without testing any of them?

 

[vletm]

Monty​

Just a thought...
Would it be clearer to join here as "AI"
if "AI" will give the answers?