Excel Gurus step inside

[Jkwellborn 6877]

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.

[blacklab 11994]

if bigger time is in h1 and smaller in i1

=(H1-MOD(H1,100))*60/100+(MOD(H1,100)) - (I1-MOD(I1,100))*60/100+(MOD(I1,100))

[idigTexas 2297]

Will the finish time always be on the same date as the start time? 

[Jkwellborn 6877]

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.

[blacklab 11994]

=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

 

[SquishMitten 10459]

Might be worth looking into an app for this. Google billable hour calculators. Couple bucks a month and you’ll be able to do a lot more with your data.

[40acredropout 2908]

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.

formula example.xlsx

[Serak The Preparer 3436]

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?

[40acredropout 2908]

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 March 20, 2021 by 40acredropout

[Serak The Preparer 3436]

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 March 20, 2021 by Serak The Preparer

[Dahobbs 17807]

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

[40acredropout 2908]

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

[NeverMarryAStripper 22030]

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

[Dahobbs 17807]

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

[Ignatius 4080]

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 March 25, 2021 by Ignatius

[Superhero 8890]

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.

 

 

 

[Da Fino 2797]

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

[Da Fino 2797]

Correction. =TEXT(F3,"00\,000") & " " & H3

[NeverMarryAStripper 22030]

That's a shit load of bees

[That Guy 972]

But is it really? Internet says 60k to a hive. We only talking about 5 hives. That’s not a shit load. 

[fattyflattie 4639]

Damn y’all some smart mofos. 

[Hornlover 8849]

3 hours ago, fattyflattie said:

Damn y’all some smart mofos. 

You think counting bees is impressive, you should see aggy use an Excel spreadsheet to calculate how many sheep to bring to a gangbang. That's truly impressive.

[shnsajax 3592]

2 hours ago, Hornlover said:

You think counting bees is impressive, you should see aggy use an Excel spreadsheet to calculate how many sheep to bring to a gangbang. That's truly impressive.

[HornOnTheBayou 8967]

[NeverMarryAStripper 22030]

16 hours ago, That Guy said:

But is it really? Internet says 60k to a hive. We only talking about 5 hives. That’s not a shit load. 

I bet if you got stung by them all you would think it was a shit load

Edited April 23, 2021 by NeverMarryAStripper

[Superhero 8890]

1 hour ago, NeverMarryAStripper said:

I bet if you got stung by them all you would think it was a shit load

[Jkwellborn 6877]

You think counting bees is impressive, you should see aggy use an Excel spreadsheet to calculate how many sheep to bring to a gangbang. That's truly impressive.

Their answer will always be all of them.

[gyroprotagonist 2595]

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.  

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

[Dahobbs 17807]

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.  

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? 

 

[gyroprotagonist 2595]

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.

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.

 

[chikin23 384]

Working with google sheets…

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

[Cheeseweasel 26438]

46 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?

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

[Nice Guy Eddie 13540]

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.

 

[Cheeseweasel 26438]

Just now, Nice Guy Eddie said:

 

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.

 

Yup. Just pick and go.

[Nice Guy Eddie 13540]

If you were in Excel, I discovered an easier way:  =SUM(Sheet1:Sheet9!B3)

I can't figure out how to do this in google sheets which I know as the request.

@Cheeseweasel @chikin23

[Reagan1k 3026]

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?  

[Nice Guy Eddie 13540]

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 December 1, 2021 by Nice Guy Eddie

[Anastasis 23498]

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. 

[Dahobbs 17807]

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

[Anastasis 23498]

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. 

[40acredropout 2908]

Good luck tonight @Nice Guy Eddie

https://www.pcworld.com/article/559001/the-future-of-esports-is-microsoft-excel-and-its-on-espn.html

[Nice Guy Eddie 13540]

22 hours ago, 40acredropout said:

Good luck tonight @Nice Guy Eddie

https://www.pcworld.com/article/559001/the-future-of-esports-is-microsoft-excel-and-its-on-espn.html

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