|
Elston Gunn posted:I have a bunch of values in a spreadsheet and I would like to add four columns between each value and then fill the empty spaces with equal amounts of the difference. Example: I've seen esquilax's post but isn't it just easier to do A1: 5 B1: A1 * 0.8 + F1 * 0.2 C1: A1 * 0.6 + F1 * 0.4 D1: A1 * 0.4 + F1 * 0.6 E1: A1 * 0.2 + F1 * 0.8 F1: 10 Then just autofill the formula down the columns B to E.
|
# ¿ Nov 4, 2011 23:27 |
|
|
# ¿ May 11, 2024 11:01 |
|
This article from microsoft offers an innovate alternate method of joining the two sets of data: http://support.microsoft.com/kb/211802 I don't know if microsoft query is still around in newer versions, it was in 2003 looking like it hadn't been updated for 10 years. You'd have to adapt the method a bit, rather than using word to create the query you'd want to use excel to import data and store it in a new spreadsheet, but it should be basically the same.
|
# ¿ Dec 13, 2011 13:10 |
|
hayden. posted:Any advice? Is there some plugin out there to make my life easier? I've added parameters to queries too complex for the Query Editor before. The path was kinda unpleasant - I saved the spreadsheet I was working on to the 2003-era SpreadsheetML format, which has very scant documentation. You can then delve into the markup for the querytable and give it the parameters you need. The easiest thing to do is create two basic example spreadsheets to use as reference, one with a simple parametrised query and one with a complex query and no parameters, then combine them with a bit of trial and error. Once you've got a SpreadsheetML version working, excel will happily convert it back into normal formats without it breaking - she lacks the interface to create them but not the capacity to run 'em. Just hope and pray you never need to modify it again... I also recall a much worse trick where the query I wanted used several named subqueries, and the parameters for the query were buried in the innermost query, so you couldn't expose them without combining the whole mess into a single monster SQL statement. The way round that was to use a sql statement like: code:
|
# ¿ Nov 14, 2012 00:35 |
|
hayden. posted:What method/tool do you use to do that?
|
# ¿ Nov 14, 2012 11:45 |
|
If you're keen to fully automate this, the List.Dates function will probably be quite helpful. You'd have to do some of the maths yourself to figure out the count of dates you need to generate, given your first and last dates in the given data, plus a bit to get to the end of the final month. Edit: Simpler still, apply Date.EndOfMonth to your max date and the hard part of the calculation is done. Heavy_D fucked around with this message at 10:19 on Feb 11, 2022 |
# ¿ Feb 11, 2022 10:16 |
|
I always add a shortcut of paste unformatted (well, paste with number formats only) to the quick access toolbar. Then you can use Alt+n instead of ctrl-v as your pasting shortcut -- where n is the position of the button in the quick access toolbar (e.g. the third button shortcut is Alt+3). You might have to search through a few screens of commands to add, but pays off in the long run.
|
# ¿ Dec 19, 2022 23:23 |
|
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 |
|
|
# ¿ May 11, 2024 11:01 |
|
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 |