Forum Discussion
IF Function Help
Oh, my, Jeremy. You've done a masterful job of taking what was a paper process and converting it to Excel. The problem is that you've done just that: taken what was a paper process and converted it to Excel.
You have done that--and I've seen this happen in other instances too--by imposing that paper process mindset onto Excel, rather than by re-conceptualizing the whole process as a database which can take advantage of Excel's abilities to report on transactions such as movements of trailers.
You've done a really good job, and your formulas that count dispatched trailers are impressive examples of the COUNTIFS function. And how you've incorporated the Page Footer into some of the tabs so as to note such anomalies as "Must Ship" is impressive. But also depressing, in that you literally are picturing this as a page of a report, with a footer that contains--in the case of "Must Ship" at any rate, something that should be its own column--an attribute belonging with a store code on the database.
For your immediate problem, the one you posted, I go back to my recommendation that you separate the SL and other codes from the number that represents a percentage. I've done it for you as a demonstration, on tab 124, and shown two examples of how you could create some cleaner summary data with far simpler formulas. As a general principle, it complicates things unnecessarily to combine two different types of data in a single cell, as you've been doing. A percent PLUS a code for disposition makes it hard to do things with either!
I've added a couple other suggestions in text boxes.
My long term recommendation would be to get yourself a book (there are many: https://smile.amazon.com/s?k=excel+as+database&ref=nb_sb_noss_2 ) on creating and using databases in Excel. You're clearly smart enough to master this, and there's no reason why you need to take a course--many good Excel users are self-taught through reading of books and manuals and "playing" with what they find there in meeting needs at work. That's how I did it, back in 1971 and 1972, before there were PCs and spreadsheets...on an IBM mainframe using a language called APL. Totally self-taught.
Is it possible for you to post a sample of your actual spreadsheet, or at the very least a simple mockup that replicates the quandary you're facing? (Not an image, an actual spreadsheet)
In the absence of that, or if you would prefer to work just from suggestions--which is fine--then let me suggest the following possibility:
- Place the SL (or any other codes) in a separate column from the percentage figures that you are counting and summing.
- You can then use SUM or SUMIF or COUNT or COUNTIF to sum or count the numeric values based on criteria referencing the separate column's codes.
If I were in your place I'd also consider getting rid of the five separate sheets, putting it all into a single database, using a single column to reflect the batch. And yes, another for date, so that a year's worth (or more) can all be in a single table. You'll be even more impressive to your bosses with how you can tabulate comparisons: quarter to quarter, month to month, week to week. Have you explored the Pivot Table feature? You'll love it.
I really haven't had much training with the pivot tables yet although I'm in MIS graduate that's nothing I delved into yet. However, I would love to know but I've attached a copy of one of my completed shift excel documents to give you an idea of what I was speaking about. Everything within the document from tab 121, 122, 123, 124, and 125 are also calculated on the last EOS tab (which is end of shift report for data keeping)
Oh, my, Jeremy. You've done a masterful job of taking what was a paper process and converting it to Excel. The problem is that you've done just that: taken what was a paper process and converted it to Excel.
You have done that--and I've seen this happen in other instances too--by imposing that paper process mindset onto Excel, rather than by re-conceptualizing the whole process as a database which can take advantage of Excel's abilities to report on transactions such as movements of trailers.
You've done a really good job, and your formulas that count dispatched trailers are impressive examples of the COUNTIFS function. And how you've incorporated the Page Footer into some of the tabs so as to note such anomalies as "Must Ship" is impressive. But also depressing, in that you literally are picturing this as a page of a report, with a footer that contains--in the case of "Must Ship" at any rate, something that should be its own column--an attribute belonging with a store code on the database.
For your immediate problem, the one you posted, I go back to my recommendation that you separate the SL and other codes from the number that represents a percentage. I've done it for you as a demonstration, on tab 124, and shown two examples of how you could create some cleaner summary data with far simpler formulas. As a general principle, it complicates things unnecessarily to combine two different types of data in a single cell, as you've been doing. A percent PLUS a code for disposition makes it hard to do things with either!
I've added a couple other suggestions in text boxes.
My long term recommendation would be to get yourself a book (there are many: https://smile.amazon.com/s?k=excel+as+database&ref=nb_sb_noss_2 ) on creating and using databases in Excel. You're clearly smart enough to master this, and there's no reason why you need to take a course--many good Excel users are self-taught through reading of books and manuals and "playing" with what they find there in meeting needs at work. That's how I did it, back in 1971 and 1972, before there were PCs and spreadsheets...on an IBM mainframe using a language called APL. Totally self-taught.
- I appreciate this; I believe implementing this process will defiantly continue to make my job more efficient. THANK YOU SO MUCH. & yes! I'm an advocate for learning anything new, once I completed my degree and the concentration of Management of Information System (which mainly organizes data) I became fanatical with implementing my learnings in my everyday life, especially at work which has been a huge help. I certainly appreciate your recommendation and I had initially planned on taking a course in EXCEL to become a more strong user however, I believe I apply the good book knowledge first per your recommendation to see how much my brain will absorb.
Thanks Again 🙂
