Nested IFS Statement to Determine Status
Our take
I use an Excel workbook (365 desktop) to track the status of correspondence that needs to hit various checkpoints (columns E, G, H, I (conditional), K). As the item moves through the process, dates are input into those respective columns; plus the occasional cancellation or item hold.
I am looking to build out Column M to provide an 'automated' location status that will change as dates or other key text are put into the columns mentioned above. I aim to be able to sort and count the correspondence by location/status.
My initial thought is to use a IFS statement to check for keywords or date by column precedence. I came up with the below but it is throwing an error.
Any help would be greatly appreciated.
Draft Formula - Throws #Name Error / [Top row below header is row 10]
=IFS(K10="Cancelled","Cancelled",K10="Hold","Hold",K10="Shutdown","Shutdown",ISDATE(K10),"Completed",ISDATE(I10),"TEXT5",ISDATE(H10),"TEXT6",ISDATE(E10),"TEXT7",ISBLANK(E10),"TEXT8")
Note: The snip shows a few of the different setups for the data rows.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- I am having a issue with a previously working formulaHi, I use Excel primarily on SharePoint to track inspections on a production floor. I have used the same formula for over a year: =IFS(D38=FALSE,"",E38="",NOW(),TRUE,E38). Essentially, when the field is checked off, it autostamps it with the time and date. (See Picture). Since Friday, however, the time and date defaults to what is shown in the picture. Troubleshooting I have tried is confirming that all devices using this sheet are time-synced, up to date, and restarted. I have confirmed that the calculation options are set to automatic and that the formatting is correct for both time and date. I have re-typed the formula as well. I am at a loss, as it was a perfectly functional formula up until Friday. Correct time stamp Error time stamp submitted by /u/Shadynasty8091 [link] [comments]
- How to +1 using IF, IFS, SWITCHI am currently trying to do a for a spreadsheet where each time a type of the same incident occurs, 1 is added to the correlating cell. I am struggling to achieve this. I have tried an IFS and SWITCH but neither work, I keep getting the same error. =IFS(A5="Animals",B5+1,A5="Appliances",B5+1,A5="Electricity",B5+1,A5="Fire",B5+1,A5="Heating",B5+1,A5="Lifts",B5+1, A5="Property Damage",B5+1,A5="Water",B5+1, A5="Other",B5+1) =SWITCH(A5,A5="Animals",B5+1,A5="Appliances",B5+1,A5="Electricity",B5+1,A5="Fire",B5+1,A5="Heating",B5+1,A5="Lifts",B5+1,A5="Property Damage",B5+1,A5="Water",B5+1,A5="Other",B5+1) It keeps saying inconsistent error. If I add equal signs in front it completely breaks. If I take the B5 out they are all "1". If I keep it in they are all "0". I just want to make a tally. Can someone help please? Example of what I am trying to achieve Excel is version 2603 build 19822.20182 EDIT 1 - SOLVED but feel free to add feedback. Solution ended up as: =IFS(A13="Animals",COUNTIF($A$4:A13,"Animals"),A13="Appliances",COUNTIF($A$4:A13, "Appliances"),A13="Electricity",COUNTIF($A$4:A13,"Electricity"),A13="Fire",COUNTIF($A$4:A13,"Fire"),A13="Heating",COUNTIF($A$4:A13,"Heating"), A13="Lifts",COUNTIF($A$4:A13,"Lifts"),A13="Property Damage",COUNTIF($A$4:A13,"Property Damage"),A13="Water",COUNTIF($A$4:A13,"Water"),A13="Other",COUNTIF($A$4:A13,"Other")) If you can make it tidier be my guest :) submitted by /u/Advanced-Jelly3774 [link] [comments]