Forum Discussion
Formula Parse Error: IFS referring to a subtotal cell
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
2 Replies
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.
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
