Forum Discussion
Issues with IFs And Formula
I have been Trying to get the correct formula for Hours and keep getting a False Error when I add the true and false out put in.
Unfortunately, those images don't show the entire formula that you've been entering. Can you describe more completely the references in your spreadsheet, what your logical tests are, and the desired results. Even better, could you also post a copy of the spreadsheet itself (so long as there's no proprietary or confidential info in it)...you can do that in Google Sheets or on OneDrive. Then grant us "edit" rights.
- What I am Trying to Accomplish is a Delivery Window report From a Daily to a whole month. I will have daily tabs Pasting data from our GPS and inputting this in to the daily spreadsheet. This will then tell me if we were early, on time, or late.
What I am trying to do next is combine this in to a Monthly report not every account goes daily so I was putting in a if formula to search for the account name and if it = that then next test is to see if they were early, on time or late. and place a letter in the box for that day.As you've noted by now, there's no need to send mtarler and me separate replies (unless they're different). This is a public forum.
That said, may I ask if you'd be open to tracking the daily deliveries all on a single sheet, a single data base? Excel works well in analyzing data when the "raw" form is contained in a single Table. It makes the formulas simpler..they still need to recognize a date and a client--easily accomplished by making data a column of its own. We humans find it easier to look at raw data split out--as if it were on a paper ledger sheet (which may well have been the genesis of this system), but Excel, computers in general can do the heavy lifting of recognizing and separating each element (On Time, Late, Early) by client and date.
- I looked at the sheet and added 2 formulas. You are getting a #N/A because the IFS statement has nothing to return because the condition is false so it tries to go to the 'next' condition and there is none. So in the 1st case I just added a "default" case by adding a condition TRUE (and hence always true) and the 'default' output. In the second case I replaced the AND with a product (*) since product of booleans is the same as an AND. Using product will return an array while AND will try to AND everything together. Sort of like the difference between A1:A3 + B1:B3 will give an array of 3 values while SUM(A1:A3,B1:B3) will return 1 value with all 6 values added together. I know you tested it with IF() and it works differently and that is another thing about how IF() and IFS() are different. IFS is like a function while IF is an operator (unless arrays are passed). So IFS evaluates all the parts of it and passes them into the function but IF will eval the condition and then eval the corresponding result. I know it is all very confusing but I hope my options help you.
- I'm not seeing the problem here. It might be that IFS works different than IF? IFS does not have a 'false' output per se. The format is IFS( condition1, if-true-1, condition2, if-true-2, ....) SO to get a 'false' output or default output I use a TRUE and then what I want so IFS( a=b, c, TRUE, d ) so if a=b then it will output c but if NOT then it goes to the NEXT condition which in this case is TRUE which means it will output d.
