Nov 07 2022 03:44 AM
I'm trying to use an IFS function to tell the document user who signs off the order based on the subtotal of another cell:
Here is my current attempt:
=IFS(G25>20000,"Email Budget Holder, Recommended by FARCO, For Authorisation by the Trust Board (plus three quotes)", G25>10000, "Email Budget Holder, Endorsement by CEO, Authorised by FARCO (plus three quotes)", G25>5000, "Spend Request Needed. Email Budget Holder and CEO (plus three quotes)", G25>3000 "Budget Holder and CFO/CEO (plus three quotes)", G25>1000,"EHT or COO (plus two quotes)", G25>500, "EHT or COO", G25<500, "Subject Lead or Budget Holder")
Here is the sum in cell G25:
=SUM(G15:G24)
Currently Returning:
#ERROR!
Formula Parse Error
Nov 07 2022 04:05 AM
Hi
It is generally not good practice to use complex IFS statements as they are difficult to decipher and maintain. You might want to consider using a VLOOKUP with the messages in a table. See example below. The VLOOKUP looks for a value approximate to the one in the table and retrieves the associated message. I hope this helps. Peter
Nov 07 2022 04:16 AM
In your IFS there is only 1 comma missing after "G25>3000" (highlighted in the formula below).
=IFS(G25>20000,"Email Budget Holder, Recommended by FARCO, For Authorisation by the Trust Board (plus three quotes)", G25>10000, "Email Budget Holder, Endorsement by CEO, Authorised by FARCO (plus three quotes)", G25>5000, "Spend Request Needed. Email Budget Holder and CEO (plus three quotes)", G25>3000 , "Budget Holder and CFO/CEO (plus three quotes)", G25>1000,"EHT or COO (plus two quotes)", G25>500, "EHT or COO", G25<500, "Subject Lead or Budget Holder")
However it's easier to apply a lookup formula which has already been suggested.