|
HootTheOwl posted:I tested it before I posted it, the times were already split out
|
# ? Nov 28, 2023 06:42 |
|
|
# ? May 23, 2024 16:22 |
|
TheLastManStanding posted:It comes up with the right answer if the days are the same, but if it spans multiple days it won't. I had two test cases, the first was the example as given, the second was from 11pm to 1am the next day.
|
# ? Nov 28, 2023 07:16 |
|
HootTheOwl posted:Again, I tested it before I posted it. It gives 554 hours instead of 2 because you are multiplying the day difference by 24; in terms of hours it is evaluating as 1-23+(24*(24))
|
# ? Nov 28, 2023 07:35 |
|
TheLastManStanding posted:
It shouldn't be evaluating the day difference to 24, it should be evaluating it to 1. I do not know why for you it is evaluating it that way in yours. here it is in my excel. This behavior matches This website. Where basic subtraction is equivalent to executing the DAYS function.
|
# ? Nov 28, 2023 16:19 |
|
HootTheOwl posted:It shouldn't be evaluating the day difference to 24, it should be evaluating it to 1. I do not know why for you it is evaluating it that way in yours. Change E30 to 11/28/23. Does it still say 2 hours? It's giving you the "right answer" because it is formatted as h:mm, which drops the number of days from the cell. If you change formatting to something custom like yyyy:mm:dd:hh:mm, you can see that the cell value is being treated in excel as January 23 1900, @ 2 AM. [h]:mm gives the elapsed number of hours. Your formula is essentially telling excel to take 1/0/1900, add 24 days, add 1/24 days , and subtract 23/24 days. Doing direct math on date and time values is a minefield of these kinds of errors, especially if you are importing data or manually entering it. It's best practice to use functions like hour() and do the math directly. esquilax fucked around with this message at 16:46 on Nov 28, 2023 |
# ? Nov 28, 2023 16:31 |
|
I still get the correct numbers when I make it 28
|
# ? Nov 28, 2023 18:11 |
|
HootTheOwl posted:It shouldn't be evaluating the day difference to 24, it should be evaluating it to 1. I do not know why for you it is evaluating it that way in yours. Subtracting is equal to days because date/times are stored as days, but you are multiplying by 24, which means now they are hours, and then adding the time difference, which is still in a unit of days. The unit mismatch is an error. If you change the resolved date to 11/28 your equation gives 143.0833333 (3434 hours) when it should just be 5.08 (122 hours).
|
# ? Nov 29, 2023 05:52 |
|
I don't know who Google has working on Sheets but they're kinda loving up my sheets. I was trying to figure out how to count all occurrences of column N that are in column D and I wrote this... code:
except what google sheets is doing now is it keeps creating new rows in the sheet. i deleted the formula once i realized what was happening but by then it had created 11000 more rows. there's been these kinda small issues for about 1-2 months now. another one, which i can't remember if this was what it did before but if you have two sheets, and you toggle between them using your keyboard (alt + up/down cursor), when you go back to the original sheet the cell that the cursor is in will move down by 1. so if it was in cell A24 on the first sheet, and you jump to the second using the keyboard shortcut, when you comeback to the first sheet the cell will be on A25 now. i'm not sure if this is as designed because if you just click the sheets regularly, it doesn't move the highlighted cell. i'm pretty sure this change was also pretty recent. i'm not sure if i like it or not though since i think there are some use cases. but it's weird that they're just doing this without mentioning these kinda changes.
|
# ? Jan 11, 2024 06:54 |
|
Any Power Query or M-Code wizards about? I'm trying my hand at Advent of Code 2023 Day 1. Given the input below, I want to capture the first and last digit in each row, concatenate them, and them sum the column. code:
|
# ? Jan 13, 2024 19:32 |
|
Hughmoris posted:Any Power Query or M-Code wizards about? I'm trying my hand at Advent of Code 2023 Day 1. The latter part is the easiest thing, there are built in functions that can probably try to parse but here's the simple and direct function code:
|
# ? Jan 14, 2024 13:48 |
|
Heavy_D posted:The latter part is the easiest thing, there are built in functions that can probably try to parse but here's the simple and direct function Thanks for the ideas! I ended up brute forcing it by clicking around the GUI. The gist of my steps:
It works but is wildly inefficient and ugly. I'm going to give your way a go. Also, here is a guy tackling it in Excel and making me feel bad.
|
# ? Jan 14, 2024 16:10 |
|
What’s a good resource for learning M language?
|
# ? Jan 14, 2024 16:20 |
|
mweber posted:What’s a good resource for learning M language? Until you find something better, this is probably a good starting point: https://www.youtube.com/watch?v=3ZkIwKBVkVE
|
# ? Jan 14, 2024 17:15 |
|
Hughmoris posted:Until you find something better, this is probably a good starting point: https://www.youtube.com/watch?v=3ZkIwKBVkVE Thanks!
|
# ? Jan 14, 2024 17:27 |
|
https://learn.microsoft.com/en-us/powerquery-m/ Is it the same as this?
|
# ? Jan 14, 2024 17:48 |
|
Hughmoris posted:Any Power Query or M-Code wizards about? I'm trying my hand at Advent of Code 2023 Day 1. Based on Heavy_D's help and this video I've settled upon a final solution, which is much cleaner than clicking through the GUI. code:
Hughmoris fucked around with this message at 19:44 on Jan 14, 2024 |
# ? Jan 14, 2024 18:22 |
|
Nice work - Text.Select is more direct than my suggestion of Text.ToList and List.Select. But I do have an extra tip! Where you havecode:
code:
Heavy_D fucked around with this message at 12:51 on Jan 15, 2024 |
# ? Jan 14, 2024 21:28 |
|
I feel like this is something thousands of people probably do in Excel every day and I just don't know the right terms to find their solutions, but maybe I'm wrong and it is complicated. I have one master sheet with 145 columns. I have a second input sheet with 101 columns, all of which are in the master sheet, all in the same order, but not all grouped together; the other 44 columns from the master sheet are mostly formulas based on those 101 columns, and appear at various points in between. The data in the input sheet is compiled weekly and appended to the master. What I'm looking for is a way to have probably another sheet, blank except for the column headers from the master sheet, that can pull the data from the input sheet into the columns with matching headers, or alternately, a way to insert blank columns in the input sheet where the master-sheet-only columns exist in the master. The end goal either way is to be able to copy the entire input sheet and paste it at the bottom of the master sheet without doing manual column organization (which is easy to screw up). Then I can just pull down the formulas in the blank columns and be done. Any thoughts would be appreciated. Note that I'm a lousy coder, if that affects your advice.
|
# ? Jan 23, 2024 23:12 |
|
Can you move all the function columns over and then copy paste into the blank space? Or you can have your blank values be equal to the input sheet (=inputSheet!A2) and then anything you paste into the input sheet will change this new sheet.
|
# ? Jan 24, 2024 00:37 |
|
It's not the most sophisticated way, but it's probably about the speed you're operating at based on your description. Use Column Groups to either make the master sheet hide the calculations columns, or insert blank columns in the Input sheet and collapse them until you're ready to copy/paste. There's a button to (un)collapse all which will save you time either way. https://www.excelcampus.com/tips/groups-outlines/
|
# ? Jan 24, 2024 00:58 |
|
I must either be explaining this wrong or completely misunderstanding, sorry.HootTheOwl posted:Can you move all the function columns over and then copy paste into the blank space? No, the column order has to be maintained, unfortunately. HootTheOwl posted:Or you can have your blank values be equal to the input sheet (=inputSheet!A2) and then anything you paste into the input sheet will change this new sheet. This is 3000–5000 rows per week. That'd be a lot of =s, and a bunch of N/As when some of them were blank in the input sheet. (It's also a fresh CSV every time, but I could rename it.) DarkHorse posted:It's not the most sophisticated way, but it's probably about the speed you're operating at based on your description. Use Column Groups to either make the master sheet hide the calculations columns, or insert blank columns in the Input sheet and collapse them until you're ready to copy/paste. There's a button to (un)collapse all which will save you time either way. This didn't work. I created a template sheet with the columns from the master sheet, grouped the columns that don't appear in the input, collapsed all, then tried to copy and paste from the input sheet. The data pasted into the collapsed columns. If a visual would be more clear, let's say this is the master sheet: And this is the input sheet: I'm looking for a less manual way to get here: from where I could just drag the formula columns down and be done.
|
# ? Jan 24, 2024 03:58 |
|
Similar question. I have to copy info from a program and paste it into an Excel tab every day, to try and keep track of a specific type of transfer we have a quota for. (Our MI sucks) I've got a different tab with enough formulas that pull from the tab with raw data to get the basic information we need (# of transfers, and who has fulfilled their quota/who hasn't). The table always ends up with some garbage in it, but I can live with that. Since I'm trying to expand use of this sheet to people who are more scared of Excel (and just polish it up), and I'm weaksauce on the VBA side, would the best option be an input form, where they can hit a button to paste clipboard contents to cell A1 of the (hidden) cell for the raw data? I've just been copying and pasting with destination formatting so far. Follow-up; is there a way in the macro to then have the data clipped at a specific point before and after the info I need? When I copy from the website, there's a few lines of junk on top and on bottom, and I just need the stuff that would, in any sane program, be exportable to text/excel. GD_American fucked around with this message at 04:14 on Jan 24, 2024 |
# ? Jan 24, 2024 04:12 |
|
disaster pastor posted:I must either be explaining this wrong or completely misunderstanding, sorry. You'll have to use your template sheet, it's going to replace your input sheet. If you're testing things out, first copy your input sheet data to the template sheet manually. Copy from the now filled out template sheet (presumably with columns collapsed) and paste into your master sheet and everything should be in the right spot. If that works, just provide the template sheet to your users as the input sheet. With columns collapsed it should be identical for their purposes. And since you're dragging down (double-clicking the + drag) formulas anyway, any errant data should be overwritten
|
# ? Jan 24, 2024 04:27 |
|
DarkHorse posted:You'll have to use your template sheet, it's going to replace your input sheet. If you're testing things out, first copy your input sheet data to the template sheet manually. Copy from the now filled out template sheet (presumably with columns collapsed) and paste into your master sheet and everything should be in the right spot. Unfortunately, the input sheet is a system-generated CSV, so I can't replace it this way.
|
# ? Jan 24, 2024 04:34 |
|
Separate post for a separate postGD_American posted:Similar question. I have to copy info from a program and paste it into an Excel tab every day, to try and keep track of a specific type of transfer we have a quota for. (Our MI sucks) You might want to look into doing a Power Query. You can set things up so that users just have to place an excel file (or even some other format, like .txt, based on how that other program makes its output!) into a specific folder and the Power Query will filter, process, format, and calculate automatically. This video goes over some of that https://youtu.be/Nbhd0B5ldJE?si=TCJtJZdeF22oRvVj
|
# ? Jan 24, 2024 04:43 |
|
disaster pastor posted:Unfortunately, the input sheet is a system-generated CSV, so I can't replace it this way. The Power Query method might be a good idea for you too then. If you find yourself repetitively processing data the exact same way on a frequent basis it's a good method for automating all those steps.
|
# ? Jan 24, 2024 04:46 |
|
DarkHorse posted:The Power Query method might be a good idea for you too then. Thanks. I've been screwing around with it all morning without success, but I'll keep looking at it.
|
# ? Jan 24, 2024 14:43 |
|
Hey thread, quick question. My mom recently opened up a wholesaler and given we live in an economy with rampant inflation, keeping up to date on supplier prices eats up a lot of her time. I'm very Excel-illiterate so I don't know if this is possible but it's essentially black magic so might as well ask: is it possible to link a value from a website (which appears on the page source) to a cell and have it stay up to date with any changes made to the website?
|
# ? Jan 25, 2024 00:36 |
|
Azran posted:Hey thread, quick question. My mom recently opened up a wholesaler and given we live in an economy with rampant inflation, keeping up to date on supplier prices eats up a lot of her time. I'm very Excel-illiterate so I don't know if this is possible but it's essentially black magic so might as well ask: is it possible to link a value from a website (which appears on the page source) to a cell and have it stay up to date with any changes made to the website? I might be sounding like a broken record at this point but you can do it with Power Query https://youtu.be/j_ONaX0Ettw?si=jKoLtcd7SHEN8jWo VBA may be more efficient and elegant but this requires less effort or expertise.
|
# ? Jan 25, 2024 00:48 |
|
Mostly yes, with Power Query. The most relevant example I can find: https://learn.microsoft.com/en-us/power-query/connectors/web/web-by-example There are a ton of caveats with web scraping but in general it works. You can get it to happen automatically but by default you'll have to hit the 'Refresh All' button on the Data tab. It may be against the website's Terms of Service, there may be a rate limit, they may just tired of the web calls and block you, etc. But I wouldn't really be worried about any of that with your use case.
|
# ? Jan 25, 2024 00:50 |
I'm performing an analysis of shipping costs at my new job. We received a report on every shipment sent in 2023, but the data was quite messy and I've been trying to clean it up. Some of our orders ship in multiple packages, and each of our 3 carriers reports that differently. FedEx (in blue) reported the full charge on the line for the first package, and a $.01 charge for every subsequent package. UPS was initially just wrong, and reported the full shipping charge for every package (red) in column AM. I fixed that with a silly formula to basically match the FedEx format (orange) in column AL. This worked for the first analysis we did, but there are problems with this hacky solution for other analyses. Our last shipper, GSO, averages the shipping charge over every line/package. I'd like to take the FedEx and UPS reporting, and change it to the GSO reporting. The reference number column (AV) has a unique first 13 digits for every order, and if an order has multiple packages it's noted in the last 2 digits (green). I've been trying to use the reference numbers to somehow define a range of shipping rates that would be averaged into a new column, but I've been running into a wall for how to do this. My last attempt was using =AVERAGEIF to define logic around that but it fell apart with a DIV/0 error, and was very hacky and I didn't like it anyway. If there's a generally accepted solution to this sort of issue I'd be happy to learn it, I've just been struggling with my search terms. Thanks!
|
|
# ? Jan 31, 2024 02:00 |
|
I think this will work: A) New column with an adjusted reference number from formula =Left(AV2, Len(AV2)-3) B) Average cost column with formula =Averageifs(AM:AM, New column from A, Row reference for new column from A) For example: If you put your new adjusted reference column in BA =AVERAGEIFS(AN:AN, BA:BA, BA2)
|
# ? Jan 31, 2024 04:20 |
Wonderful, that was a much more elegant fix than anything I was working through. The helper column clarified things significantly. Now I need to read up more about how to use *IFS functions.
|
|
# ? Jan 31, 2024 05:13 |
|
Nice - I use Excel every day at work and wasn’t even aware IFS existed, I’m still writing tiered IF statements like it’s 1996.
|
# ? Jan 31, 2024 07:23 |
|
I love COUNTIFS, but I wish they could use ranges of different sizes.
|
# ? Feb 3, 2024 15:50 |
|
I have a simple Excel sheet to track my spending vs my income, with each month's transactions set up in columns ascode:
|
# ? Feb 8, 2024 04:42 |
|
C-Euro posted:I have a simple Excel sheet to track my spending vs my income, with each month's transactions set up in columns as If they were all in one sheet, this would be a good use case for a pivot table. Multiple sheets complicate it a lot. Are you wedded to that design?
|
# ? Feb 8, 2024 05:07 |
|
Off the top of my head I think you might need to pull everything into a single page to do it that way, then you could use Averageif / Avergaeifs formula. You could write a macro to pick up sheet, append to master list - or manually do that each month. Doing it with regular formulas would get out of hand quickly, maybe it would work with Indirect… you have a list of your sheets on your formula page and then use something like =sumif(indirect(sheetname!a2:a200), “mortgage”, indirect(sheetname!b2:b200) where sheetname is just the cell that holds the sheet names. fake edit: I was stumped enough to google and if you search “Averageifs across multiple sheets” you’ll find lots of answers using Sum and Frequency - seems like that’s the way.
|
# ? Feb 8, 2024 05:09 |
|
Hmm I'm guessing Excel doesn't let you glob two columns together into a list or an array? Cause you can do that in Google Sheets.
|
# ? Feb 8, 2024 05:50 |
|
|
# ? May 23, 2024 16:22 |
|
Get power bi, it's free. Then you can just use power query to join them all, and make a really slick looking dashboard.
|
# ? Feb 8, 2024 07:07 |