In which @Captain asks questions about Microsoft Office
-
What's the best way to make an Excel sheet where one column depends on data from two other columns?
In particular, I have data like:
A | B | C T | F | ? T | F | ? F | T | ?
and I want the first question mark to represent OR(A1, B1), the second question mark to be OR(A2, B2), etc.
There must be some way to "zip" the two columns together... right? Or will I have to type out my formula 50-something times?
-
That's almost literally what I would have typed, but you'll need to truth-ify the values...
-
What @Tsaukpaetra said. Also, when you copy a cell to a new row or column, it assumes the column letter or row number is relative and changes it, unless you prefix it with a
$
. E.g.A$1
would lock the1
row while leaving the column number relative,$A1
would keep the column constant while leaving the row relative,$A$1
would always reference A1 even if you copied the value somewhere else.
-
@Captain I don't have excel in front of me but it would be something like
=IF(OR(A1="T",B1="T"),"T","F")
Then double click the bottom right corner to copy the formula down
-
@Captain said in In which @Captain asks questions about Microsoft Office:
Or will I have to type out my formula 50-something times?
Convert it to a table. Fill in the formula in a new column. Watch as Excel expands it downwards. No need to fiddle with '$' unless Excel gets it wrong.
Get it made 'pretty' as a bonus.Or use the '+' sign of a selected cell to drag the formula down.
-
@Captain said in In which @Captain asks questions about Microsoft Office:
Or will I have to type out my formula 50-something times?
Who taught you Excel and left "fill down" off the curriculum?
-
@error said in In which @Captain asks questions about Microsoft Office:
What @Tsaukpaetra said. Also, when you copy a cell to a new row or column, it assumes the column letter or row number is relative and changes it, unless you prefix it with a
$
. E.g.A$1
would lock the1
row while leaving the column number relative,$A1
would keep the column constant while leaving the row relative,$A$1
would always reference A1 even if you copied the value somewhere else.And just to save you some time typing all those $-signs:
Press F4 while the cursor is at the position of the cell's address. First press of F4 will add the $-sign to both column and row, subsequent presses will change that to locked-row-only and locked-column-only.
-
@Jaloopa said in In which @Captain asks questions about Microsoft Office:
@Captain I don't have excel in front of me but it would be something like
=IF(OR(A1="T",B1="T"),"T","F")
Then double click the bottom right corner to copy the formula down
Almost. In Excel you use the semicolon to separate the arguments, thus:
=IF(OR(A1="T";B1="T");"T";"F")
-
@Rhywden said in In which @Captain asks questions about Microsoft Office:
Excel you use the semicolon to separate the arguments,
Wat? Not in the formula bar?
-
@Luhmann said in In which @Captain asks questions about Microsoft Office:
Or use the '+' sign of a selected cell to drag the formula down.
@Rhywden said in In which @Captain asks questions about Microsoft Office:
Press F4 while the cursor is at the position of the cell's address. First press of F4 will add the $-sign to both column and row, subsequent presses will change that to locked-row-only and locked-column-only.
Learning all kinds of shortcuts from this thread.
Filed under: Also learning that NodeBB quotes the entire post even if you highlight one line.
-
OK, here's a harder one.
I filled in the table, and now I want to count the number of
True
s andFalse
s in theC
column (and divide to have a rough completion ratio).But there are two problems:
- How do I treat
#VALUE!
asFalse
instead ofundefined
? - How do I count?
- How do I treat
-
@Captain said in In which @Captain asks questions about Microsoft Office:
How do I treat #VALUE! as False instead of undefined?
Iferror(b2. False)
-
-
@Tsaukpaetra said in In which @Captain asks questions about Microsoft Office:
@Rhywden said in In which @Captain asks questions about Microsoft Office:
Excel you use the semicolon to separate the arguments,
Wat? Not in the formula bar?
And once again, localization bit me. Okay, it's the semicolon as a separator when you're using the German variant because we're using the comma as a decimal separator.
Should've known.
-
@Tsaukpaetra said in In which @Captain asks questions about Microsoft Office:
@Captain said in In which @Captain asks questions about Microsoft Office:
How do I count?
Sumif
Specifically:
=SUMIF(C1:C10, TRUE)/SUMIF(C1:C10,FALSE)
-
@blakeyrat said in In which @Captain asks questions about Microsoft Office:
Who taught you Excel and left "fill down" off the curriculum?
I had literally never heard of that until a couple months ago when someone linked Joel Spolsky's "You suck at Excel" video.
-
@FrostCat It's among the first things I taught my pupils :)
-
@Rhywden I've never needed to use Excel enough to have made taking a course worth it, though.
-
I took a class in high school, so I knew it was possible, but I had no idea what keyword to search for. Also, it's too bad Excel calls it some random term instead of "a zip".
-
@FrostCat At my school, it's a mandatory course in Office. And I'm doing my best to inspire them to use Powerpoint correctly and only where it's appropriate.
Also trying to get them to follow the 1-2-3 rule regarding backups. After I have repeated that one at least once, "my dog ate my homework" does not count anymore ;)
-
@Captain said in In which @Captain asks questions about Microsoft Office:
Also, it's too bad Excel calls it some random term instead of "a zip".
What the fuck is "a zip"? I've been using computers for decades, and I've never heard that terminology. And even from context here, I'm not sure what the heck you mean by it.
-
@blakeyrat it's a colloquial way to say a "convolution". Take two lists (typically with the same number of elements) and produce a list by applying an operator pairs of items (one from each list).
-
@Captain said in In which @Captain asks questions about Microsoft Office:
it's a colloquial way to say a "convolution".
I've never heard of that either.
At the very bottom of the first page of Google results, I get this:
A convolution is an integral that expresses the amount of overlap of one function g as it is shifted over another function f. It therefore "blends" one function with another.
Which I'm not even sure matches the definition you gave just now.
-
@Captain Not sure that this would be an appropriate term, as it is rather an intelligent expansion as you can use the same technique to quickly create a sequence of numbers / dates / ...
Thus it's rather more like an extrapolation.
-
@blakeyrat What you're reading does match up with what I said, but this is a better page for you: https://en.wikipedia.org/wiki/Convolution_(computer_science)
-
@Captain Yes, but it's not a convolution what Excel does - I think you're misinterpreting a bit.
What you are doing is indeed a convolution - taking stuff from two lists and then using those to map to a new list.
What Excel does is a bit different, however: It merely takes your selection and looks at it to make an educated guess on how you'd most likely want to expand your selection. It doesn't care what is outside your selection - it only looks at the selection itself. Only after the expansion, then it calculates the results.
That's also why you may end up with invalid values when referencing singular cells without a $.
-
@Captain Ok this is me being a self-taught moron idiot stupid dummy-face, but I honestly have never come across that term in the entire time I've been programming computers both as a hobby and a profession. Since it's a terrible article (even by Wikipedia standards) and refers specifically to Haskell and doesn't appear anywhere in the first page of Google results for "convolution", I'm assuming it's useless crap hipster programmers use to distract themselves from writing actual useful programs that get shit done. That said:
It also seems to have absolutely nothing to do with what "fill down" in Excel does. "Fill down" basically has a little bit of AI to try and determine what the "next item" in your sequence is, and automatically inserts that into the cell below the current one.
For example, if your selection is:
1/1/2016 1/2/2016
Fill Down will choose:
1/3/2016
as the next value.If it's:
January March
it'll fill in the next cell with
May
, thenJuly
, etc.If you only have a single cell selected, it'll just duplicate that cell into the cell below.
(I say "below," but it also works horizontally, FYI.)
This is extremely basic Excel 101 stuff. In fact, this feature is for all practical purposes the reason Excel still exists and, for example, Lotus 1-2-3 does not.
-
@blakeyrat said in In which @Captain asks questions about Microsoft Office:
@Captain Ok this is me being a self-taught moron idiot stupid dummy-face,
I definitely wasn't going there...
but I honestly have never come across that term in the entire time I've been programming computers both as a hobby and a profession.
Um ok. Still waiting to see where this is going...
Since it's a terrible article (even by Wikipedia standards) and refers specifically to Haskell and doesn't appear anywhere in the first page of Google results for "convolution",
The most important convolution operator is the convolution integral you were reading about on Mathworld. It's used in Fourier analysis and probability theory and other things. I'd guess just about every article you'd find on convolutions would be about:
- Fourier analysis
- Signals theory
- Probability
I'm assuming it's useless crap hipster programmers use to distract themselves from writing actual useful programs that get shit done.
Ah huh. It's more like a specialized loop that got factored out of common problems. You know, so you can focus on solving problems instead of typing dumb and pointless logic.
To wit: if Excel didn't have a way to fill in the next value with the right formula, I would have had to type the formula myself about 50 times.
That said:
It also seems to have absolutely nothing to do with what "fill down" in Excel does. "Fill down" basically has a little bit of AI to try and determine what the "next item" in your sequence is, and automatically inserts that into the cell below the current one.
Yeah, I get that. But I wanted to calculate a convolution, and apparently "fill down" and its magic AI is apparently the only way to do it.
This is extremely basic Excel 101 stuff. In fact, this feature is for all practical purposes the reason Excel still exists and, for example, Lotus 1-2-3 does not.
Um ok I'm the doodie dummy moron head who doesn't know basic Excel. We get it.
-
@Captain said in In which @Captain asks questions about Microsoft Office:
Yeah, I get that. But I wanted to calculate a convolution, and apparently "fill down" and its magic AI is apparently the only way to do it.
Excel isn't designed to be used by anybody who casually throws out the word "convolution".
-
@blakeyrat Just FYI, but https://msdn.microsoft.com/en-us/library/dd267698(v=vs.100).aspx.
It's not a very common operation, but it's common enough even C# has a method for it.
-
@Captain said in In which @Captain asks questions about Microsoft Office:
enough even C# has a method for it.
Yet the link provided doesn't use the word "convolute" in any forme anywhere...
-
@Tsaukpaetra sure, and @blakeyrat asked me what "a zip" is, and I said it's another word for a convolution operator...
-
@Captain said in In which @Captain asks questions about Microsoft Office:
Also, it's too bad Excel calls it some random term instead of "a zip".
So in other words, you convoluted a process done in Excel to something completely different that did not apply and in fact was misleading, yet now you're and putting the blame for the confusion on @blakeyrat for being confused about a term that doesn't apply?
Ok. Gotcha.
-
@Tsaukpaetra HUH? Read the thread.
-
Also, no blame mentioned anywhere...
-
Convolution is exactly the right word for this discussion.
-
@Captain said in In which @Captain asks questions about Microsoft Office:
Also, it's too bad Excel calls it some random term instead of "a zip".
That would be a terrible name for the Office audience ?
Quick, whats a zip ?- the thingie in the pants
- a compressed file
Even in CS I guess 'zip' is not / was not so much know in the sense you used, because Python has a zip function that does something different: given k iterators, it returns an iterator over k-tuples when component i comes from iterator i
Yeah, naming is hard.
-
@Magus said in In which @Captain asks questions about Microsoft Office:
Convolution is exactly the right word for this discussion.
In that we're taking two data sets and merging them using an operator?
From my perspective, I thought we were talking about using Excel effectively and reducing redundant work.
From @Captain's perspective (interpreted, extrapolated), it seems he had the end result in mind first and foremost and wanted to apply concepts and terms he was actually doing, and the interim information and terminology relating to Excel being a side note of "huh, so that's what it's called".
-
@Tsaukpaetra said in In which @Captain asks questions about Microsoft Office:
From @Captain's perspective (interpreted, extrapolated), it seems he had the end result in mind first and foremost and wanted to apply concepts and terms he was actually doing, and the interim information and terminology relating to Excel being a side note of "huh, so that's what it's called".
I think he wants everybody in the universe to use Haskell and LISP, and everything he posts here is a stealthy way of encouraging that. Even if it looks like he's asking for Excel advice.
-
@Tsaukpaetra I'm using it in the sense "to make convoluted: That is, confusing and dumb"
Python calls c#'s zip map afaik.
-
@Luhmann said in In which @Captain asks questions about Microsoft Office:
Convert it to a table.
^ This. You should definitely tell Excel that those cells are a table. You get stuff like sorting and filtering for free and your formulas will become a lot more readable since you can now use column names in them.
-
@cabrito said in In which @Captain asks questions about Microsoft Office:
@Captain said in In which @Captain asks questions about Microsoft Office:
Also, it's too bad Excel calls it some random term instead of "a zip".
That would be a terrible name for the Office audience ?
Quick, whats a zip ?- the thingie in the pants
- a compressed file
Even in CS I guess 'zip' is not / was not so much know in the sense you used, because Python has a zip function that does something different: given k iterators, it returns an iterator over k-tuples when component i comes from iterator i
That's what a zip function does... the thing that makes a function "a zip" is that the function takes similarly shaped data structures and traverses them in parallel, taking values from each position and combining them. Combining the values by putting them in a
k
-tuple is formally equivalent to applying ak
-variable function to each member. You can define them in terms of each other.Yeah, naming is hard.
How does a zipper work? You pull on the tab, and it brings opposite links a little track/chain thing together, and links them together. It's a pretty close analogy to what zips do. Think of pulling elements out of two parallel lists and pairing them together.
-
@asdf I guess tables are my next Excel topic.
-
@Rhywden said in In which @Captain asks questions about Microsoft Office:
Press F4 while the cursor is at the position of the cell's address. First press of F4 will add the $-sign to both column and row, subsequent presses will change that to locked-row-only and locked-column-only.
That's amazing. How did I not know that. :D
-
@Rhywden said in In which @Captain asks questions about Microsoft Office:
@Tsaukpaetra said in In which @Captain asks questions about Microsoft Office:
@Captain said in In which @Captain asks questions about Microsoft Office:
How do I count?
Sumif
Specifically:
=SUMIF(C1:C10, TRUE)/SUMIF(C1:C10,FALSE)
Guys.
If you want to count, FFS use COUNTIF. Only use SUMIF if you want to add numbers together.
Also, for future reference, there are COUNTIFS and SUMIFS if you want to have multiple conditions. But be careful with SUMIFS, the order of the parameters is different to what you would expect from SUMIF (the column to be summed goes first).
There's also a SUM(IF(...)) syntax you can enter as an array formula which is the old way of doing these things. It's not as nice but in some circumstances you have to do it that way, for instance if what you want to sum is a complex combination of columns.
-
@Scarlet_Manuka said in In which @Captain asks questions about Microsoft Office:
There's also a SUM(IF(...)) syntax you can enter as an array formula which is the old way of doing these things. It's not as nice but in some circumstances you have to do it that way, for instance if what you want to sum is a complex combination of columns.
And don't forget
SUMPRODUCT(array1, [array2], [array3], ...)
for doing:running balances...
= SUMPRODUCT( --( Journal_Dt <= A31 ), WFJ_DB ) - SUMPRODUCT( --( Journal_Dt <= A31 ), WFJ_CR )
complex "selects"...
=SUMPRODUCT( --( 'Reserve Analysis.xlsm'!RAO_Valid_fm <= VALUE( TEXT( A31, "YYYYMM" ) ) ), --( 'Reserve Analysis.xlsm'!RAO_Valid_to > VALUE( TEXT( A31, "YYYYMM" ) ) ), 'Reserve Analysis.xlsm'!RAO_Reserve )
-
@Scarlet_Manuka said in In which @Captain asks questions about Microsoft Office:
There's also a SUM(IF(...)) syntax you can enter as an array formula which is the old way of doing these things. It's not as nice but in some circumstances you have to do it that way, for instance if what you want to sum is a complex combination of columns.
Using an
IF
inside of aSUM
didn't work right for me until I used thearcane ritualintuitive and discoverable shortcut of pressing Ctrl+Shift+Enter after entering my formula. Apparently this made it an "array formula", which made things work correctly — until I edited it and pressed Enter again, silently converting it back to a regular formula. At least it didn't give me some kind of error, it just quietly started giving me wrong answers.
-
@error Yep, array formulas are tricky things to get working properly, which is why SUMIF and COUNTIF were great when they came out - they take care of 90% or more of the cases that you used to have to do an array formula for, and are much easier to use.
-
That F4 thing is neat. I'll contribute a tidbit.
CTRL+;
Enters the current date in the current cell.
-
@Captain said in In which @Captain asks questions about Microsoft Office:
HUH? Read the thread
I think this is another case of @captain's mind working differently to the rest of the planet, possibly due to Haskell contamination