MickinMD ★ Posted January 5, 2022 Share #1 Posted January 5, 2022 I have been using the same stock and budget spreadsheets for years and haven't had to play with functions for a while. So I knew what I could do but couldn't remember the functions names. It's nice to know there are a lot of online sites to answer questions like: How do find the last non-zero number in an Excel column?" It's fun if you've got plenty of time to figure out how to adapt them to your situation, plugging in different variations and observing the effect. Since the last non-zero number in the "TOTAL CALORIES" column initially only represents breakfast, I needed to also do a Daily Calorie Average through the previous day, meaning I had to subtract 1 from the number of days with entries ("COUNTIF") and then find the last non-zero number ("LOOKUP") so I could subtract it from the total Calories: 2 Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 5, 2022 Share #2 Posted January 5, 2022 Excel rocks 1 Link to comment Share on other sites More sharing options...
Airehead Posted January 6, 2022 Share #3 Posted January 6, 2022 Excel scares me. I have hired great bright people to create these works of art. I just use them 1 Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 6, 2022 Share #4 Posted January 6, 2022 1 minute ago, Airehead said: Excel scares me. I have hired great bright people to create these works of art. I just use them At work I'm an excel genius 2 Link to comment Share on other sites More sharing options...
Airehead Posted January 6, 2022 Share #5 Posted January 6, 2022 1 minute ago, bikeman564™ said: At work I'm an exce genius need a new job? Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 6, 2022 Share #6 Posted January 6, 2022 2 minutes ago, Airehead said: need a new job? I'm listening 😁. 1 Link to comment Share on other sites More sharing options...
Airehead Posted January 6, 2022 Share #7 Posted January 6, 2022 school calendar with 10 months plus 2 weeks over the 6 weeks of summer 1 Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 6, 2022 Share #8 Posted January 6, 2022 4 minutes ago, Airehead said: school calendar with 10 months plus 2 weeks over the 6 weeks of summer Should be a template available for that 1 Link to comment Share on other sites More sharing options...
Airehead Posted January 6, 2022 Share #9 Posted January 6, 2022 Just now, bikeman564™ said: Should be a template available for that people like jobs were they get four weeks off in the summer and holiday break, and a week in February, and spring break Plus four weeks of regular vacation 1 Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 6, 2022 Share #10 Posted January 6, 2022 1 minute ago, Airehead said: people like jobs were they get four weeks off in the summer and holiday break, and a week in February, and spring break Plus four weeks of regular vacation Lemme look tomorrow. I have office 365 at work,. But only 2010 at home. 1 Link to comment Share on other sites More sharing options...
Bikeguy Posted January 6, 2022 Share #11 Posted January 6, 2022 11 minutes ago, bikeman564™ said: Should be a template available for that Something like this? I have Office Professional Plus 2016 on my laptop. One of the last perks from work. I got the DVD for $25, an incentive to do more work at home. 1 Link to comment Share on other sites More sharing options...
Bikeguy Posted January 6, 2022 Share #12 Posted January 6, 2022 9 hours ago, MickinMD said: Since the last non-zero number in the "TOTAL CALORIES" column initially only represents breakfast, I needed to also do a Daily Calorie Average through the previous day, meaning I had to subtract 1 from the number of days with entries ("COUNTIF") and then find the last non-zero number ("LOOKUP") so I could subtract it from the total Calories: Why not set up the total column to be an empty value unless all 4 of your eating categories (breakfast, lunch, dinner, snacks) are filled in? Then you can just use =AVERAGE() Or.... =AVERAGEIF() can be used. Link to comment Share on other sites More sharing options...
JerrySTL ★ Posted January 6, 2022 Share #13 Posted January 6, 2022 So many people use Excel as a database and Access as a spreadsheet that Microsoft is going combine them. The new program will be called Microsoft Excess. 1 1 2 Link to comment Share on other sites More sharing options...
Airehead Posted January 6, 2022 Share #14 Posted January 6, 2022 The calendar was me tempting @bikeman564™ to come to work for me. This would be his work schedule. 1 1 Link to comment Share on other sites More sharing options...
ChrisL Posted January 6, 2022 Share #15 Posted January 6, 2022 I’m pretty handy with excel but @MickinMD post just doesn’t compute in my brain. That’s like saying fun with algebraic functions… 2 Link to comment Share on other sites More sharing options...
Further Posted January 6, 2022 Share #16 Posted January 6, 2022 14 minutes ago, ChrisL said: I’m pretty handy with excel but @MickinMD post just doesn’t compute in my brain. That’s like saying fun with algebraic functions… Fun means different things to different people Much different... 1 1 Link to comment Share on other sites More sharing options...
Square Wheels Posted January 6, 2022 Share #17 Posted January 6, 2022 52 minutes ago, JerrySTL said: So many people use Excel as a database and Access as a spreadsheet that Microsoft is going combine them. The new program will be called Microsoft Excess. So few will get this. 4 Link to comment Share on other sites More sharing options...
Bikeguy Posted January 6, 2022 Share #18 Posted January 6, 2022 @bikeman564™ Have you ever used this before? This was a new for me. I guess you can teach an old dog new tricks. I've used LOOKUP, VLOOKUP, and HLOOKUP I've never seen a range defined by It took me a while to find this way to define a lookup array. 1 Link to comment Share on other sites More sharing options...
Ralphie ★ Posted January 6, 2022 Share #19 Posted January 6, 2022 6 hours ago, Square Wheels said: So few will get this. I am guilty as charged of using excel as a database. Aboot all I do in it is filter stuff. Mordac took access away from us after someone had built an excellent database for us. For some reason IT departments have always seemed to hate Access. One of my favourite memories is in the early pc days of the early 80s when dBase III was big, a cow-orker complained that his whole database turned into a dot! I remember the dBase III license was always very picky and cranky. Many were. Often after you did a tape backup it would deactivate your license. Oh joy. That might be some of the computer PTSD I sometimes get when I just don’t trust software sometimes! 3 Link to comment Share on other sites More sharing options...
BR46 Posted January 6, 2022 Share #20 Posted January 6, 2022 18 hours ago, MickinMD said: I have been using the same stock and budget spreadsheets for years and haven't had to play with functions for a while. So I knew what I could do but couldn't remember the functions names. It's nice to know there are a lot of online sites to answer questions like: How do find the last non-zero number in an Excel column?" It's fun if you've got plenty of time to figure out how to adapt them to your situation, plugging in different variations and observing the effect. Since the last non-zero number in the "TOTAL CALORIES" column initially only represents breakfast, I needed to also do a Daily Calorie Average through the previous day, meaning I had to subtract 1 from the number of days with entries ("COUNTIF") and then find the last non-zero number ("LOOKUP") so I could subtract it from the total Calories: I've been using the same stock and budget spreadsheet for almost 40 years. I still have fun playing with her fractions so I know what I can and can't do although I forgot the fraction names. I have fun plugging in a variety of different things and observing the outcome. It's nice to know that there are a lot of online sites that can answer my questions with videos. If I start eating too many calories she will remind me of my carbohydrate and calorie limit. 1 2 Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 6, 2022 Share #21 Posted January 6, 2022 8 hours ago, Bikeguy said: @bikeman564™ Have you ever used this before? This was a new for me. I guess you can teach an old dog new tricks. I've used LOOKUP, VLOOKUP, and HLOOKUP I've never seen a range defined by It took me a while to find this way to define a lookup array. Specifically I'm not sure. I have used LOOKUP and VLOOKUP in the past. As I've mentioned, several years ago I created a bike log. I wanted totals based on dates (months), so it took some research but I used SUMPRODUCT to do so. I had twelve cells w/ each months total distance, and 12 cells w/ each month's total time. I also added a checkbox next to the mileage entry that if checked, it was a trainer ride. I then added up miles that were indoor only (using an IF function). It's pretty sweet actually, and I'm impressed with it. It did take some time to get it working properly, but I did To me, sitting at home wrenching on this type of stuff is great. I have a general ledger on Excel I created that I still use, and it's not super complicated, but does have some IFs and conditional formatting in there As powerful of a tool Excel is, I'm lucky if I use 2% of its capabilities. I've created bike gearing charts w/ cadence & speed. And I created tire pressure/temperature calculator. So when riding in cold, specifically my fat bike, I wanted to see what the pressure would be, and I keep it on my phone. I type in the inside & outside temps, and inside tire pressure. It then gives me what the pressure will be outside and what to inflate the tire to There's apps & charts for this, but I prefer to create this type of stuff myself, because I can 1 Link to comment Share on other sites More sharing options...
JerrySTL ★ Posted January 6, 2022 Share #22 Posted January 6, 2022 11 hours ago, Square Wheels said: So few will get this. Years ago I posted it to a large Access web site on April 1. Some minor hell broke loose. I think that some foreign countries don't celebrate April Fools Day. 3 Link to comment Share on other sites More sharing options...
2Far ★ Posted January 6, 2022 Share #23 Posted January 6, 2022 I love the following formula (stolen from the interwebs). It returns the name/value of the worksheet tab the cell is located in. It's useful (to me) on my expense account workbook where the tab is the week-ending date, the formula fills the appropriate reference date in the sheet. I use it in my time-sheet workbook and my monthly accounting of the Temp & Sub-contractor workers. =MID(@CELL("Filename",I7),SEARCH("]",@CELL("Filename",I7),1)+1,32) 2 Link to comment Share on other sites More sharing options...
bikeman564™ Posted January 6, 2022 Share #24 Posted January 6, 2022 At work we have accrued vacation, however we can use it any time. The only time this would be used if someone's no longer w/ the company, then the vacation time would be balanced. Anywho just for fun..yeah fun, I created this equation to show me how many days I have accrued as of "today", or a date that I enter. This is it. It's the first time I used the CEILING function which rounds a number up to a nearest multiple of significance. Since our vacation time can be used in 2 hour increments, I have it to the nearest 1/4 day. 1 Link to comment Share on other sites More sharing options...
2Far ★ Posted January 6, 2022 Share #25 Posted January 6, 2022 I use a lot of named ranges, some are single cells others are Rows & Columns. I do use Vlookup & Hlookup a lot, too. When I was at the chemical company, I created a workbook that was opened from another program. The workbook would execute a macro automatically when opened, collect the data as required, then run a macro to SaveAs itself with a timestamped name before it auto closed. It was pretty nifty. 1 1 Link to comment Share on other sites More sharing options...
Ralphie ★ Posted January 6, 2022 Share #26 Posted January 6, 2022 I knew a few people who were wizes with Excel VBA mackerals. It was amazing what they could do. 1 Link to comment Share on other sites More sharing options...
Bikeguy Posted January 6, 2022 Share #27 Posted January 6, 2022 6 minutes ago, Philander Seabury said: I knew a few people who were wizes with Excel VBA mackerals. It was amazing what they could do. Over the years at work, I used Excel macros to do LOTS of stuff at work. Then one day I needed some data from our mainframe billing computer. I had to tell the IT guy exactly what I wanted, he built a query and the data gets dumped into an Access data table. So then I needed to learn Access to create, append, etc... data tables. When that was done, I'd export the data to be used in Excel. So I got back to my 'comfort' zone. Yeah... I was the Excel guy in our work group. 2 Link to comment Share on other sites More sharing options...
MickinMD ★ Posted January 6, 2022 Author Share #28 Posted January 6, 2022 19 hours ago, Bikeguy said: Why not set up the total column to be an empty value unless all 4 of your eating categories (breakfast, lunch, dinner, snacks) are filled in? Then you can just use =AVERAGE() Or.... =AVERAGEIF() can be used. Thanks, That's something I'll keep in mind, but in this case that means I can't see the average of any days unless all four daily calorie cells of the last day are filled. It also requires a more complicated formula since I'd have to identify what cells go into AVERAGEIF since if will change each day. I could do it by relating it to the date. I could set it up so that it uses the last day's average if all 4 aren't filled. I'll save it for a day I want to play with functions! Thanks again - I cut and pasted your post and my thoughts to the bottom of the spreadsheet. Link to comment Share on other sites More sharing options...
Bikeguy Posted January 7, 2022 Share #29 Posted January 7, 2022 5 hours ago, MickinMD said: t also requires a more complicated formula since I'd have to identify what cells go into AVERAGEIF since if will change each day. I vote for simple. Use data to hide any incomplete days. Like just use your 'snack' column to suppress any totals you that are incomplete. If you don't eat any snacks (which would be a goo thing, but I digress) just remember to enter zero. As you found zero is a value. In this example rows 7, 8, and 9 do have formulas in column F. Have fun.... 1 Link to comment Share on other sites More sharing options...
Ralphie ★ Posted January 7, 2022 Share #30 Posted January 7, 2022 KISS would dictate using something like My Fitness Pal. Link to comment Share on other sites More sharing options...
Square Wheels Posted January 7, 2022 Share #31 Posted January 7, 2022 58 minutes ago, Philander Seabury said: KISS would dictate using something like My Fitness Pal. That's my vote. Doesn't get easier. 1 Link to comment Share on other sites More sharing options...
Ralphie ★ Posted January 7, 2022 Share #32 Posted January 7, 2022 8 minutes ago, Square Wheels said: That's my vote. Doesn't get easier. Of course it is sort of fun to screw around with excel. But lately my patience for anything that is snot super easy has waned. Google and Apple and the SW forum have spoiled me. Undoubtedly part of their eville plans. Darn big tech! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now