Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

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:
5 10 becomes 5 6 7 8 9 10

I've tried using regression but the data isn't fit very well using linear or polynomial equations.

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

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 08:37

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

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

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

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:

EXECUTE OuterQuery ? ?

and use the same SpreadsheetML trick to bind two parameters to the statement. I was pretty amazed it worked at all.

# ¿ Nov 14, 2012 00:35

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

hayden. posted:

What method/tool do you use to do that?

Notepad.

# ¿ Nov 14, 2012 11:45

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

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

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

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

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

Hughmoris posted:

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:
1abc2    // 12
pqr3stu8vwx  //38
a1b2c3d4e5f  //15
treb7uchet // 77

Total is 12+38+15+77 = 142
I can't figure out how to split and check if each character is a number or not.

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:

//given a single character text as input, check if it's from 0-9
isCharADigit= (text Char) => Char >= "0" and Char <= "9"

There are a number of different strategies you could employ for the splitting - a lot of the challenge here is figuring out how to work around M's lack of loop constructs. Text.At(input,0) will give you the first character, and you could use this with a recursive function that checks the next index for Text.At if it's not a digit. Or you could use Text.ToList to convert the input to a list of characters, List.Select to filter the list to just digit characters, and List.First/List.Last to take the outermost digits. If you were really brave you could use List.Accumulate...

# ¿ Jan 14, 2024 13:48

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 08:37

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

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 have

code:

    Digits = List.Transform({0..9}, each Number.ToText(_)),

You don't need to define an "each" function in this instance; since Number.ToText is already a one parameter function you can just pass the function name.

code:

    Digits = List.Transform({0..9}, Number.ToText),

Heavy_D fucked around with this message at 12:51 on Jan 15, 2024

# ¿ Jan 14, 2024 21:28

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread