Jump to content

Excel Gurus step inside


Recommended Posts

I need some help. I need to make a spread sheet to show how long it takes to do something. I need to enter the start and finish time in military time and have the difference figured in minutes. This is just a simple subtraction between the two cells correct?

 

A coworker was trying to do this and somehow fucked it all to hell.

 

Basically, 0830 and 0900 = 30 minutes, 0830 and 1100 = 150 minutes. That’s pretty much what I’m looking at.

Link to comment
Share on other sites

Yes there will be times when the finish time is the next day. We are trying to track load times for trucks at work. My boss wants to do it this way. It’s a pretty simple spreadsheet as far as what we are putting in.
Load #, loader, start time, finish time and elapsed time.

I’m trying to talk them into using a damn computer because I don’t want to enter this shit manually. I don’t have time for that.

But we have two shifts and the evening shift is there until all the trucks have been loaded and checked out. Or 6am. Whichever comes first.

Link to comment
Share on other sites

=if ( (H1-MOD(H1,100))*60/100+(MOD(H1,100)) - (I1-MOD(I1,100))*60/100+(MOD(I1,100))>0, (H1-MOD(H1,100))*60/100+(MOD(H1,100)) - (I1-MOD(I1,100))*60/100+(MOD(I1,100)) . (H1-MOD(H1,100))*60/100+(MOD(H1,100)) - (I1-MOD(I1,100))*60/100+(MOD(I1,100)) + 1440 )

will take care of times that cross over midnight

I'm sure you could simplify it some

 

Link to comment
Share on other sites

  • 1 year later...

Calling on the wizards.  I'm usually able to find solutions with a google search but am running into a wall.

Below is a simple example of what I'm trying to accomplish.  I have a large set of data (200 columns, 25,000 rows) and I am trying to write a formula that will return the max value given the criteria of 1) time period, 2) city, and 3) statistic (column), which will all be selected from a dropdown;  goal is for the formula to dynamically calculate based off these inputs and refresh to correct answer when assumptions change.  .  I was able to figure out how to do this for the sum and average but am stumped here.

image.png.344c234e276a8600c21b649512130df8.png

formula example.xlsx

Link to comment
Share on other sites

45 minutes ago, Serak The Preparer said:

When you say 200 columns, do you mean you have Stat 1, Stat 2, ..., Stat 200 for example? So that in your actual data set you are trying to figure out which Stat is the largest for a given city/year?

Correct that here are ~200 columns of different statistics.  But not trying to identify the statistic that is the largest in a given city / year.  Trying to find the largest value in the specific statistic column that is defined in a dropdown assumption (cell C5 in the example) for that city in those years.    So cell C6 is the answer I would be looking for, I just don't know how to write that formula.  Hopefully that makes sense?  Thanks for taking a look. 

Edited by 40acredropout
Link to comment
Share on other sites

49 minutes ago, 40acredropout said:

Correct that here are ~200 columns of different statistics.  But not trying to identify the statistic that is the largest in a given city / year.  Trying to find the largest value in the specific statistic column that is defined in a dropdown assumption (cell C5 in the example) for that city in those years.    So cell C6 is the answer I would be looking for, I just don't know how to write that formula.  Hopefully that makes sense?  Thanks for taking a look. 

There may be a more elegant solution but what I would suggest is having a Maxif for each individual column (meaning all 200 maxifs are being calculated), then a simple hlookup or index/match so that only the dropdown stat is shown.

Edited by Serak The Preparer
  • Hook 'Em 1
Link to comment
Share on other sites

5 hours ago, 40acredropout said:

Correct that here are ~200 columns of different statistics.  But not trying to identify the statistic that is the largest in a given city / year.  Trying to find the largest value in the specific statistic column that is defined in a dropdown assumption (cell C5 in the example) for that city in those years.    So cell C6 is the answer I would be looking for, I just don't know how to write that formula.  Hopefully that makes sense?  Thanks for taking a look. 

So, is this for you or for other people? It is simple enough to write a formula that will get the answer you want, but identifying the specific column is a bit more complex.

First, turn your data into a table. You can do that by selecting anywhere in your data and pressing control 't' or highlighting everything you want to be in table and then pressing control 't'. The latter is only important if you have empty columns or rows that excel will read as a break in the range. 

Here is your formula once you've created the table:

=MAXIFS(table1[Stat A],Table1[City],$C$4,Table1[Year],">="&$C$2,Table1[Year],"<="&$C$3)

Based on your previous worksheet, your table shouldn't start until row 9. You don't want it to include your simple form (B2 through C6). Enter in the formula above into C6. the only thing you'd have to do is manually change the part that says "Stat A" to whatever Stat reference you wanted (based on the column name). In your sample worksheet that means changing "Stat A" to "Stat B" or whatever you want. Having the selected stat column adjust based on another cell reference isn't trivial, or at least I don't know a simple way to do it. So, if it is just for you, I'd do this and make that one small adjustment. Your other filters (city, start, and end) can be drop downs without issue. 

It'd actually be simpler if instead of having 200 columns, you had all your stats in one column with another column that identified the stat type. Then you could just use the maxifs function to filter based on stat type and you could have your stat type be a drop down. Your table would have a lot more rows of course.

The other option is similar to what Serak suggested. Create your data table as discussed above. Right after the last row of data in your first stat column enter the following formula (my example assumes "Stat A" is the name of the first stat column):

=MAXIFS([Stat A],Table1[[City]:[City]],$C$4,Table1[[Year]:[Year]],">="&$C$2,Table1[[Year]:[Year]],"<="&$C$3)

You can then drag that across the rest of the columns and it'll create a maxifs formula for each column based your identified criteria in C2 through C4. 

In C6 enter the following:

=INDEX(Table1[#Totals],1,MATCH($C$5,Table1[#Headers],0))

Whatever you enter in C5 will determine which maxifs column is displayed. I would turn your headers into a list for data validation on c5 and do the same for your cities in c4. 

I've attached the latter for your example sheet

formula example - Edit.xlsx

  • Hook 'Em 2
Link to comment
Share on other sites

@Serak The Preparer@Dahobbs

Thanks guys-  both of these get the job job and I'm going to roll with it.  It's annoying to me because it seems like it should be easier than it is.  I think about the way you can use an index(match,match) function to find a value across both an array with both row and column conditions... seems like you should easily be able to use that same functionality to include a sumif / maxif/ averageif/ whatever function over an array selected by said condition but....  I'm stumped and cannot find an example of doing that anywhere.

Anyways, thanks again for taking a look and helping me get what I needed.  

Link to comment
Share on other sites

46 minutes ago, 40acredropout said:

@Serak The Preparer@Dahobbs

Thanks guys-  both of these get the job job and I'm going to roll with it.  It's annoying to me because it seems like it should be easier than it is.  I think about the way you can use an index(match,match) function to find a value across both an array with both row and column conditions... seems like you should easily be able to use that same functionality to include a sumif / maxif/ averageif/ whatever function over an array selected by said condition but....  I'm stumped and cannot find an example of doing that anywhere.

Anyways, thanks again for taking a look and helping me get what I needed.  

You can. Just not the way you have your data arranged, at least not without some custom code (there also might be some way to do it using sumproduct, but I'm not smart enough to figure it out). But, you can use excel power query to unpivot your stat columns and then your maxifs function will work exactly like you want. Example:

 

formula example - Edit2.xlsx

Link to comment
Share on other sites

On 3/22/2021 at 10:49 AM, NeverMarryAStripper said:

It seems to me that a pivot table would be the way to go

If you have the flexibility to have your dataset have a ‘stat’ column with a value of A, B, or C (as opposed to having it spread across three columns) this is definitely the way to go. At that point it’s the max of a single column and everything else can be set with filters/slicers (I think the latter look better)....

Edited by Ignatius
Link to comment
Share on other sites

  • 4 weeks later...

Need some formatting help...

I want to combine 2 cells (one number, one text). e.g.

Cell F3:  300000 
Cell H3: Bees

If I use the formula   =F3 & " " & H3     I get "300000 Bees"

But I want my output to be  "300,000 Bees" with a comma.

How do I get the comma, throw me a freakin' bone here.

 

 

 

Link to comment
Share on other sites

25 minutes ago, Superhero said:

Need some formatting help...

I want to combine 2 cells (one number, one text). e.g.

Cell F3:  300000 
Cell H3: Bees

If I use the formula   =F3 & " " & H3     I get "300000 Bees"

But I want my output to be  "300,000 Bees" with a comma.

How do I get the comma, throw me a freakin' bone here.

 

 

 

=TEXT(F3,"00\,000.00") & " " & H3

Link to comment
Share on other sites

bump

so is this possible with excel?  I have a table with Weeks for col headers and qtys in certain weeks.  I want to create a cell (Text col) that will list each qty by week.  

image.png.e13940c728b6053e78e74717f401c3b7.png

Is there a way/function to do this?  Parse a row, grab non-zero values, and then combine the qty with the corresponding week.  I think i could do a massive IF chain tied to INDEX, but wondered if there was more elegant solution

Link to comment
Share on other sites

29 minutes ago, gyroprotagonist said:

bump

so is this possible with excel?  I have a table with Weeks for col headers and qtys in certain weeks.  I want to create a cell (Text col) that will list each qty by week.  

image.png.e13940c728b6053e78e74717f401c3b7.png

Is there a way/function to do this?  Parse a row, grab non-zero values, and then combine the qty with the corresponding week.  I think i could do a massive IF chain tied to INDEX, but wondered if there was more elegant solution

I'm not quite understanding why you'd need an INDEX function at all. Based on your example, you're not looking at other rows. You're just parsing each row within itself. That said, assuming you have 52 weeks, I'm not entirely sure you could use a single cell to add all the IF functions as there is a character limit. But it looks like it should be ok if you use the following:

=IF([@1]>0,[@1]&" WK"&Table1[[#Headers],[1]]&";","")&IF([@2]>0,[@2]&" WK"&Table1[[#Headers],[2]]&";","")&IF([@3]>0,[@3]&" WK"&Table1[[#Headers],[3]]&";","")&IF([@4]>0,[@4]&" WK"&Table1[[#Headers],[4]]&";","")&IF([@5]>0,[@5]&" WK"&Table1[[#Headers],[5]]&";","")

Just continue the pattern to 52. I did this assuming you make this an Excel Table named "Table1" and with the headers you have in the first row rather than just a range. 

I really have no idea why you'd want to do this though. It seems like there is probably a better way to format your data and accomplish your task. Why do you have multiple rows in the first place? What is the point of the Text column? 

 

  • Hook 'Em 1
Link to comment
Share on other sites

1 hour ago, Dahobbs said:

I'm not quite understanding why you'd need an INDEX function at all. Based on your example, you're not looking at other rows. You're just parsing each row within itself. That said, assuming you have 52 weeks, I'm not entirely sure you could use a single cell to add all the IF functions as there is a character limit. But it looks like it should be ok if you use the following:

=IF([@1]>0,[@1]&" WK"&Table1[[#Headers],[1]]&";","")&IF([@2]>0,[@2]&" WK"&Table1[[#Headers],[2]]&";","")&IF([@3]>0,[@3]&" WK"&Table1[[#Headers],[3]]&";","")&IF([@4]>0,[@4]&" WK"&Table1[[#Headers],[4]]&";","")&IF([@5]>0,[@5]&" WK"&Table1[[#Headers],[5]]&";","")

Just continue the pattern to 52. I did this assuming you make this an Excel Table named "Table1" and with the headers you have in the first row rather than just a range. 

I really have no idea why you'd want to do this though. It seems like there is probably a better way to format your data and accomplish your task. Why do you have multiple rows in the first place? What is the point of the Text column? 

 

thanks, i will take a look at that.  i was able to brute force it in a similar fashion.  I used a fixed INDEX(1,1)... INDEX(1,22) where you have the [@1].  and yes just parsing each row individually. 

Risking causing a coma from boredom, each row is Purchase Order (part #, qty, initial date).  There are multiple POs for each part #.  The table beside the 'Text' col (Table 1) is pulling from a condensed table that only has 1 row for each part # and has the total amount product available each week.  The function of Table1 is to split up the condensed data into chunks that correspond to each PO and put those chunks into the right weeks.  easier to see below.  Condensed table on top and Table 1 on the bottom.

image.thumb.png.43e2bd1a5ec734f7eeff873a0b14b5af.png

The Text part of it is the result of any changes on how the POs are currently loaded.  The group that makes all these changes wants the data in that format, so I was trying to automate it.  I don't doubt there is a better way to ultimately input the data, but I am not digging into their SAP crap if I don't have to.

 

Link to comment
Share on other sites

  • 4 months later...
52 minutes ago, chikin23 said:

Working with google sheets…

How do I take the value of cell b3 across 10 different sheets and add it together on one sheet?

 

1 minute ago, Cheeseweasel said:

Just type:  ='tabname1'!b3+'tabname2'!b3+....

And to make it extra easy, you only have to touch the keyboard to hit the equal and plus signs. Click on the cells to auto populate the tab and cell in the formula.

 

  • Like 1
Link to comment
Share on other sites

  • 2 months later...

I don't spend much time in Excel - In fact I suck at it and never had any real training....just trial and error.

I have a large file.....1000's of rows but only 5 columns.

I'm trying to print pages with only 50 rows per page and all columns, so that the printed sheets are less compressed and easier to read.

I know how to insert manual page breaks, but is there an easy way to have Excel automatically format the printing at 50 rows per page to make this shit easier to read when I need to print it?  

Link to comment
Share on other sites

2 hours ago, Reagan1k said:

I don't spend much time in Excel - In fact I suck at it and never had any real training....just trial and error.

I have a large file.....1000's of rows but only 5 columns.

I'm trying to print pages with only 50 rows per page and all columns, so that the printed sheets are less compressed and easier to read.

I know how to insert manual page breaks, but is there an easy way to have Excel automatically format the printing at 50 rows per page to make this shit easier to read when I need to print it?  

How many rows total? Divide the # by 50, and set it to print on that many pages in the page setup in the printer window.

Example: 3500 rows divide by 50 = 70 pages.

 

Additional: If you want to quickly see how many rows, click on cell A1. Hold down the Ctrl key and hit the down arrow. You will be taken to the last row to see the # of rows. Ctrl + up arrow takes you back to the top.

Edited by Nice Guy Eddie
  • Hook 'Em 2
Link to comment
Share on other sites

9 minutes ago, Anastasis said:

Anybody have a good and extensible template for creating forest plots in excel?  Need to plot like 50 variables.  Seem some step by step tutorials, but too many clicks. Found a template but it is a pain in the ass to extend to include enough variables. 

I can direct you to other step by step guides and/or provide a simple template, but it seems they are probably similar/same to what you found previously. It'll also depend on whether you just need the data charted or whether you also need the statistical analysis done.  There are a few paid plugins you can buy that may provide you the easier path you're looking for:

http://www.meta-analysis-made-easy.com/

http://www.statanalysis.co.uk/meta-analysis.html

I haven't used them myself, so I can't vouch for them. 

Here are a few of the guides I'm sure you already found:

https://scholarworks.umass.edu/cgi/viewcontent.cgi?article=1304&context=pare

https://bmcresnotes.biomedcentral.com/articles/10.1186/1756-0500-5-52

  • Like 1
Link to comment
Share on other sites

39 minutes ago, Dahobbs said:

I can direct you to other step by step guides and/or provide a simple template, but it seems they are probably similar/same to what you found previously. It'll also depend on whether you just need the data charted or whether you also need the statistical analysis done.  There are a few paid plugins you can buy that may provide you the easier path you're looking for:

http://www.meta-analysis-made-easy.com/

http://www.statanalysis.co.uk/meta-analysis.html

I haven't used them myself, so I can't vouch for them. 

Here are a few of the guides I'm sure you already found:

https://scholarworks.umass.edu/cgi/viewcontent.cgi?article=1304&context=pare

https://bmcresnotes.biomedcentral.com/articles/10.1186/1756-0500-5-52

I don't need to run the analysis or depict weights, just plot hazard ratios with confidence intervals. Thanks for the links. 

Link to comment
Share on other sites

22 hours ago, 40acredropout said:

Ha. I’ve run across excel experts that make me feel like I know zero about excel. I’m also not photogenic like Laila on YouTube 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...