Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Trying to write a IFS function looking at columns with IF formulas in them already

Brass Contributor

Hi,

Can i do a IFS formula looking at cells that already have IF formula in them

 

This formula works (except for 1 cell) if i over type the formula in P4 and/or 'Correct Fee 

 

=IFS(OR(ISBLANK(P4)<[@[Correct Fee]]), "Too low", OR(P4<[@[Correct Fee]]), "Too Low",
OR(P4>=[@[Correct Fee]]), "OK")

 

 

but i need the existing formulas which are:

 

=IFERROR(VLOOKUP([@ref],'Sheet1'!$E:$M,9,0),"")

 

Correct Fee column

=IFS(AND([@Sample]=1,[@Block]<=499),"45",AND([@Sample]=1,[@Block]<=999),"52",AND([@Sample]=1,[@Block]<=1749),"66",AND([@Sample]=1,[@Block]>=1750),"80",AND([@Pets]<1,[@Block]<=499), "35", AND([@Sample]<1,[@Block]<=999), "42", AND([@Sample]<1,[@Block]<=1749), "56",AND([@Sample]<1,[@Block]>=1750), "70")

 

 

 

Help please

2 Replies

@SGeorgie 

If you want to create an IFS formula in column P that considers the existing formulas in columns P and 'Correct Fee', you can use the IF function inside the IFS function. It seems like you want to check the value in P4 against the 'Correct Fee' and return specific values based on conditions.

Here is an example of how you might structure your formula:

=IFS( OR(ISBLANK(P4), P4 < [@[Correct Fee]]), "Too Low", P4 >= [@[Correct Fee]], "OK", TRUE, "Some other condition" )

This formula checks if P4 is blank or less than the value in 'Correct Fee'. If true, it returns "Too Low". If P4 is greater than or equal to 'Correct Fee', it returns "OK". You can add more conditions as needed.

Make sure to replace "Some other condition" with the logic you want to apply if none of the previous conditions are met.

Remember that IFS evaluates conditions in order, and the first condition that is true will be the result. Also, it stops evaluating conditions once it finds the first true condition. If your conditions are mutually exclusive, this approach should work for you.

NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together. Maybe it will help you further in your project, if not please just ignore it.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@SGeorgie 

Perhaps you could give bit more details.

Correct Fee formula looks like

=IFS(
    AND([@Sample] = 1, [@Block] <= 499),
    "45",
    AND([@Sample] = 1, [@Block] <= 999),
    "52",
    AND([@Sample] = 1, [@Block] <= 1749),
    "66",
    AND([@Sample] = 1, [@Block] >= 1750),
    "80",
    AND([@Pets] < 1, [@Block] <= 499),
    "35",
    AND([@Sample] < 1, [@Block] <= 999),
    "42",
    AND([@Sample] < 1, [@Block] <= 1749),
    "56",
    AND([@Sample] < 1, [@Block] >= 1750),
    "70"
)

Skipping that it could be simplifies, why [@Pets] inside instead of [@Sample] ? Plus, most probably, you shall use numbers instead of texts. For example, 70, not "70".

Next formula,  if i over type the formula in P4 and/or 'Correct Fee - there is this formula, in P4 or within the Table?

Assuming within the Table it probably shall look like

=IF( OR($P$4="", $P$4 < [@[Corrected Fee]]), "Too Low", "Ok")

ISBLANK() doesn't work if you return empty string for now result.

Anyway, it's not clear what combine with what.