Issues with IFs And Formula

[ChrisHrubes]

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.

[#2]

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.

[#3]

@ChrisHrubes 

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.

[#4]

Delivery Report July 2022.xlsx@mathetes 

[#5]

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.

[#6]

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.

[#7]

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.

[#8]

@ChrisHrubes 

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.

[#9]

I am open to a having a all  the daily reports on one sheet and the second sheet to show the monthly total.

Chris 

[#10]

@ChrisHrubes 

Here (attached) is a start, using a single database for all individual days, and a single formula copied across the monthly summary. That formula takes advantage of the FILTER function, which does require a new version of Excel.

I only did the first four days of data, but that should be sufficient to demonstrate the process.

The formula is this:

=FILTER(Table1[Delivery Status],(Table1[Date]=Monthly!F$2)*(Table1[Accounts Name]=Monthly!$D3),"")

And what that does is filter the column containing "Delivery Status" and show the result where

• the date matches the date in row 2, at the top of the column (F$2 accomplishes that; changing to G$2, H$2, etc., as you copy the formula across all of the monthly dates)
• the account matches the account in column D ($D3, which retains the D but increments the row number as it gets copied down, so covering any number of accounts)

This does assume that there's no more than one delivery per account per day.

Here's a good reference for learning the FILTER function. https://www.youtube.com/watch?v=9I9DtFOVPIg

[#11]

This is Great Thank you so much for your help

[#12]

One last question is that I would need to Self Populate the began and end times depending on what account is listed with out manually putting them in each time. What would be the Formula for this?

[#13]

I was assuming that the daily data was already under control, since you mentioned something about importing GPS data or something like that......If the begin and end time for each location is always fixed, then you could use a table and a VLOOKUP or something like that to populate the daily data from that other table. If that doesn't make sense to you, then please come back with a more complete description of how that is working now.

[#14]

Getting a Spill Error for the hours to began

@ChrisHrubes 

[#15]

@ChrisHrubes 

You need to get acquainted with all of the many ways that Excel can look things up in tables. Probably the most basic function is VLOOKUP, which I've used here. There are more sophisticated ways, XLOOKUP, INDEX and MATCH....among them. 

If you make the table larger, you'll need to modify the size the the referenced range in the formula, but as with the earlier example, this gets you started.

[#16]

Thank you so much for the help. is there any site or classes you would recommend to take to help me get acquainted much better.

[#17]

@ChrisHrubes 

A really good site is exceljet.com. I've started a query for lookups here: https://exceljet.net/search?query=lookup

You can also find lots of good resources on YouTube if you prefer video, with Leila Gharani one of the better

https://www.youtube.com/c/LeilaGharani