Jump to content

Fun with Excel Functions


MickinMD
 Share

Recommended Posts

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:

427408767_CaloriesTable-funwithExcelfunctions.thumb.JPG.151b39fec72e2457901674400038034f.JPG

  • Like 2
Link to comment
Share on other sites

Just now, bikeman564™ said:

Should be a template available for that:dontknow:

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

  • Like 1
Link to comment
Share on other sites

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. 

  • Like 1
Link to comment
Share on other sites

11 minutes ago, bikeman564™ said:

Should be a template available for that:dontknow:

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.

image.thumb.png.db82b9813662ed191b6a54833d9cd548.png

 

  • Like 1
Link to comment
Share on other sites

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.

 image.thumb.png.b44abae342c5c87cb46f3693f95d2b5f.png

 

Link to comment
Share on other sites

@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 image.png.0a3525cfd6f9bd1ecf5a4bd1a858e289.png

It took me a while to find this way to define a lookup array.    

image.thumb.png.6378aceec5dd39bb04b8edd070eaf22f.png

 

  • Like 1
Link to comment
Share on other sites

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. :D  That might be some of the computer PTSD I sometimes get when I just don’t trust software sometimes!

 

  • Like 3
Link to comment
Share on other sites

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:

427408767_CaloriesTable-funwithExcelfunctions.thumb.JPG.151b39fec72e2457901674400038034f.JPG

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.

  • Like 1
  • Awesome 2
Link to comment
Share on other sites

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 image.png.0a3525cfd6f9bd1ecf5a4bd1a858e289.png

It took me a while to find this way to define a lookup array.    

image.thumb.png.6378aceec5dd39bb04b8edd070eaf22f.png

 

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 :D There's apps & charts for this, but I prefer to create this type of stuff myself, because I can ;)

 

image.png.ab4d9e4e811e13dad9a797ae3d6cfd45.png

 

  • Like 1
Link to comment
Share on other sites

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.

  • Like 3
Link to comment
Share on other sites

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)

  • Like 2
Link to comment
Share on other sites

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.

image.png.4f73a7d1a9401e311d3b0c509a1db457.png

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
  • Awesome 1
Link to comment
Share on other sites

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.  

  • Like 2
Link to comment
Share on other sites

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.

 image.thumb.png.b44abae342c5c87cb46f3693f95d2b5f.png

 

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

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....

image.thumb.png.6ef4c80987c718a62dc9abe76dcd6b46.png

  • Like 1
Link to comment
Share on other sites

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!  :D

 

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...