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

«‹›82 »

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Probably not the best way but a quick (slow) and dirty way would be to loop down the rows. Grabing string and comparing them. If you can presort the list its easier. Grab string, split it down or just slice the bits you want to compare. Pop the relevant bits on a new sheet. Find any others with the same start/identifier. Add its value to the new one.

Probably a quicker way of doing this but logically itd work.

# ? Sep 7, 2014 09:11

Adbot: ADBOT LOVES YOU

# ? May 20, 2024 12:38

khazar sansculotte: May 14, 2004

I managed to get it done by sorting the list and using a "Do While cell < 5450" loop down the rows of the column containing the account/subaccount numbers. Slow, but it worked. Thanks!

# ? Sep 7, 2014 17:34

cadmaniak: Nov 21, 2006; Le God

Is it possible to produce a graph with a dynamic rolling year range where the data is entered in columns rather than rows? e.g. A1 = Jan 14, B1 Feb 14 etc

I can get it to work fine for a rolling year period by rows with the formula below, adding new months onto the subsequent row but I'd like to do the same where I enter data for the next month in the next column to produce the same results.

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

Most of the guides tell you how to do it by rows (which I can do), but I haven't been able to do it the opposite way..can anyone help?

# ? Sep 15, 2014 09:39

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

cadmaniak posted:

Is it possible to produce a graph with a dynamic rolling year range where the data is entered in columns rather than rows? e.g. A1 = Jan 14, B1 Feb 14 etc

I can get it to work fine for a rolling year period by rows with the formula below, adding new months onto the subsequent row but I'd like to do the same where I enter data for the next month in the next column to produce the same results.

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

Most of the guides tell you how to do it by rows (which I can do), but I haven't been able to do it the opposite way..can anyone help?

Is there a reason you cannot transpose the data down? Excel can be finicky sometimes and there is no easy way jumping out at me.

Even if you have to just use a temporary table as an intermediate.

# ? Sep 15, 2014 12:57

schmagekie: Dec 2, 2003

cadmaniak posted:

Is it possible to produce a graph with a dynamic rolling year range where the data is entered in columns rather than rows? e.g. A1 = Jan 14, B1 Feb 14 etc

I can get it to work fine for a rolling year period by rows with the formula below, adding new months onto the subsequent row but I'd like to do the same where I enter data for the next month in the next column to produce the same results.

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

Most of the guides tell you how to do it by rows (which I can do), but I haven't been able to do it the opposite way..can anyone help?

Try replacing the $A:$A with $1:$1.

Are you using that for a dynamic named range? If so, charts require a reference to the sheet and name, such as, =Sheet1!DynamicNamedRange.

# ? Sep 16, 2014 05:22

Chutch: Jan 1, 2008; <img src="https://forumimages.somethingawful.com/images/newbie.gif" border=0>

I made this code

=IF(I65="";0;'3.063'!$J$44)

and i want to drag/autofill for the cells down, but i want the IF function for the cell below to become like this:

=IF(I66="";0;'3.064'!$J$44)

I made the logical test look at the cell beneath (I66), but I can't make the value_if_false clause go for worksheet 3.064.

Am i making any sense and is there a way to do this?

# ? Sep 16, 2014 14:10

schmagekie: Dec 2, 2003

Chutch posted:

I made this code

=IF(I65="";0;'3.063'!$J$44)

and i want to drag/autofill for the cells down, but i want the IF function for the cell below to become like this:

=IF(I66="";0;'3.064'!$J$44)

I made the logical test look at the cell beneath (I66), but I can't make the value_if_false clause go for worksheet 3.064.

Am i making any sense and is there a way to do this?

You need to use the INDIRECT function.

I tested this in Excel, but typed it on my phone, so hopefully it works.
=IF(I65="",0,INDIRECT("'"&3+(ROW()-2)/1000&"'!J44"))

schmagekie fucked around with this message at 19:25 on Sep 16, 2014

# ? Sep 16, 2014 18:22

Chutch: Jan 1, 2008; <img src="https://forumimages.somethingawful.com/images/newbie.gif" border=0>

schmagekie posted:

You need to use the INDIRECT function.

I tested this in Excel, but typed it on my phone, so hopefully it works.
=IF(I65="",0,INDIRECT("'"&3+(ROW()-2)/1000&"'!J44"))

I see what you did there. You made some kind of formula for calculating 3.004, right? As far as I understand that is not what I am looking for. Let me try this way, and tell me if I am wrong:

Here's the syntax:

=IF(logical_test;[value_if_true];[value_if_false]), where

logical_test is: I65="" which means "I65 cell is blank"
[value_if_true] is: 0 which means if I65 is blank then result is 0
[value_if_false] is '3.064'!$J$44 which means if I65 contains ANYTHING then it should result in the value from cell J44 (locked with $'s) from another worksheet named 3.064

I have a workbook with many worksheets with titles starting from 3.001 to approx 3.114. I can copy paste the above mentioned syntax and change '3.064' to '3.065', but thought there might be a simpler way by being able to drag and fill for all cells.

# ? Sep 16, 2014 20:52

schmagekie: Dec 2, 2003

Chutch posted:

I see what you did there. You made some kind of formula for calculating 3.004, right? As far as I understand that is not what I am looking for. Let me try this way, and tell me if I am wrong:

Here's the syntax:

=IF(logical_test;[value_if_true];[value_if_false]), where

logical_test is: I65="" which means "I65 cell is blank"
[value_if_true] is: 0 which means if I65 is blank then result is 0
[value_if_false] is '3.064'!$J$44 which means if I65 contains ANYTHING then it should result in the value from cell J44 (locked with $'s) from another worksheet named 3.064

I have a workbook with many worksheets with titles starting from 3.001 to approx 3.114. I can copy paste the above mentioned syntax and change '3.064' to '3.065', but thought there might be a simpler way by being able to drag and fill for all cells.

That code should work as intended if you paste it in row 65, then copy it where needed.
For I116, it should grab J44 from sheet 3.114.

schmagekie fucked around with this message at 21:23 on Sep 16, 2014

# ? Sep 16, 2014 21:18

A Tartan Tory: Mar 26, 2010; You call that a shotgun?!

Hi folks, wondering if you can clarify something for me because I have less excel experience than I would like.

I'm trying to convert the following excel line into code and I really need to understand how it works.

code:

=MAX(SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14,0)/R14

Now as far as I understand this, the SUMPRODUCT will multiply those numbers in the array with each other (m15 * n15 * r15) and add all the multiplications together to get a sum to use with N14 and finally R14.

What I am struggling to figure out is where the MAX comes into play, as I haven't used it before. How does it effect the calculation and in what order does it actually do things? Does it only use the the maximum value generated by the SUMPRODUCT?

A Tartan Tory fucked around with this message at 09:05 on Sep 17, 2014

# ? Sep 17, 2014 08:50

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

A Tartan Tory posted:

Hi folks, wondering if you can clarify something for me because I have less excel experience than I would like.

I'm trying to convert the following excel line into code and I really need to understand how it works.
code:
=MAX(SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14,0)/R14
Now as far as I understand this, the SUMPRODUCT will multiply those numbers in the array with each other (m15 * n15 * r15) and add all the multiplications together to get a sum to use with N14 and finally R14.

What I am struggling to figure out is where the MAX comes into play, as I haven't used it before. How does it effect the calculation and in what order does it actually do things? Does it only use the the maximum value generated by the SUMPRODUCT?

The MAX will pick the max/largest number of SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14 or 0. The order it would perform the formula is:
SUMPRODUCT
/ N14
MAX of above result and 0
Result of above / R14

# ? Sep 17, 2014 11:05

A Tartan Tory: Mar 26, 2010; You call that a shotgun?!

headcas3 posted:

The MAX will pick the max/largest number of SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14 or 0. The order it would perform the formula is:
SUMPRODUCT
/ N14
MAX of above result and 0
Result of above / R14

Aha, thank you so much!

It was the 'or 0' part I couldn't work out, the order has helped a lot as well.

# ? Sep 17, 2014 11:29

Tots: Sep 3, 2007

Looking for some ideas on how to do the following:

Find a matching value in Table 2 from Table 1, then populate a field in the row being checked in Table 1 with data from a field in the matching row in Table 2.

Or visually, my inputs and desired output:

code:

Table 1                                    Output Table

|----UID----|---Col1---|                  |----UID----|---Col1---|---Col8---|
|          1|       Foo|                  |          1|       Foo|       Bop|
|          2|       Bar|                  |          2|       Bar|  SlapSlop|
|          3|    FooBar|    Output --->   |          3|    FooBar|      Slop|
|          4|    BarFoo|                  |          4|    BarFoo|      Slip|
|          5|    BooFar|                  |          5|    BooFar|   Slippty|
************************                  ***********************************

Table 2

|---Col1---|---Col2---|
|    BarFoo|      Slip|
|       Bar|  SlapSlop|
|    FooBar|      Slop|
|    BooFar|   Slippty|
|       Foo|       Bop|
***********************

E: I believed I solved this using:

code:

=INDEX(Table2!A:B,MATCH(Table1!B2,Table2!A:A,0),2)

Still taking suggestions in case this is retarded.

Tots fucked around with this message at 18:04 on Sep 19, 2014

# ? Sep 19, 2014 17:05

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Tots posted:

code:

Table 1                                    Output Table

|----UID----|---Col1---|                  |----UID----|---Col1---|---Col8---|
|          1|       Foo|                  |          1|       Foo|       Bop|
|          2|       Bar|                  |          2|       Bar|  SlapSlop|
|          3|    FooBar|    Output --->   |          3|    FooBar|      Slop|
|          4|    BarFoo|                  |          4|    BarFoo|      Slip|
|          5|    BooFar|                  |          5|    BooFar|   Slippty|
************************                  ***********************************

Table 2

|---Col1---|---Col2---|
|    BarFoo|      Slip|
|       Bar|  SlapSlop|
|    FooBar|      Slop|
|    BooFar|   Slippty|
|       Foo|       Bop|
***********************

E: I believed I solved this using:

code:

=INDEX(Table2!A:B,MATCH(Table1!B2,Table2!A:A,0),2)

Still taking suggestions in case this is retarded.

Congratulations, you discovered Index-Match on your own. It is a common solution when VLookup won't work. But if you don't already know VLookup that would have solved this particular issue. http://www.techonthenet.com/excel/formulas/vlookup.php

EDIT: On reread that may have sounded sarcastic. The congratulations are real.

# ? Sep 19, 2014 18:47

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

Tots posted:

E: I believed I solved this using:
code:
=INDEX(Table2!A:B,MATCH(Table1!B2,Table2!A:A,0),2)
Still taking suggestions in case this is retarded.

You found one of Excel's two most beautiful function combinations. It's vlookup for cool kids.

Vlookup has limitations, such as that the index must be in numerical order. Index + Match gives 0 fucks about that and just throws down like ain't nobody got time for sorting.

I really love index + match and it's absolutely how I'd do this.

# ? Sep 20, 2014 09:23

fosborb: Dec 15, 2006; Chronic Good Poster

index + match is also faster than vlookup. This is helpful because if you're doing something in Excel where that speed difference actually matters, it's a good indication you're being asked to do something way beyond the toolset your company is willing to invest in and it's time to look for a new job.

# ? Sep 21, 2014 05:52

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

fosborb posted:

index + match is also faster than vlookup. This is helpful because if you're doing something in Excel where that speed difference actually matters, it's a good indication you're being asked to do something way beyond the toolset your company is willing to invest in and it's time to look for a new job.

This is exactly what I was thinking. I always use vlookup because years of having to deal with massive garbage spreadsheets have crushed my spirit and I simply cannot bring myself to care about how much better index and match are.

Vlookup does not require the index to be sorted unless you use approximate match, as you would expect.

# ? Sep 21, 2014 23:00

Isurion: Jul 28, 2007

Index + match has the advantage that since the array you're selecting from is a cell reference you can drag the formula if you want to get the data from adjacent columns. With vlookup you have to either type in the column number by hand as an integer or use a helper row. Plus the match column can be to the right of the data column. Not really dealbreakers for vlookup but they are a couple nice quality of life things.

# ? Sep 22, 2014 04:18

schmagekie: Dec 2, 2003

Isurion posted:

Index + match has the advantage that since the array you're selecting from is a cell reference you can drag the formula if you want to get the data from adjacent columns. With vlookup you have to either type in the column number by hand as an integer or use a helper row. Plus the match column can be to the right of the data column. Not really dealbreakers for vlookup but they are a couple nice quality of life things.

You can drag vlookup if you use the COLUMN() function, such as: =+VLOOKUP(D1,A:A,COLUMN()-4,0)

# ? Sep 22, 2014 05:13

Isurion: Jul 28, 2007

schmagekie posted:

You can drag vlookup if you use the COLUMN() function, such as: =+VLOOKUP(D1,A:A,COLUMN()-4,0)

Good point, never thought of doing that.

# ? Sep 22, 2014 06:00

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

schmagekie posted:

You can drag vlookup if you use the COLUMN() function, such as: =+VLOOKUP(D1,A:A,COLUMN()-4,0)

That will work as long as you don't insert any columns.

# ? Sep 22, 2014 14:31

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

Old James posted:

That will work as long as you don't insert any columns.

Can you name the column and refer to the name?

I'm not actually sure, I can't try this right now

# ? Sep 23, 2014 14:49

schmagekie: Dec 2, 2003

Nam Taf posted:

Can you name the column and refer to the name?

I'm not actually sure, I can't try this right now

You can take the column of what you're trying to grab. =VLOOKUP($A2,Data!$A:$J,COLUMN(Data!B2),0)
e: assuming the lookup range starts in col 1, otherwise you'll have to do a little thinking.

schmagekie fucked around with this message at 16:41 on Sep 23, 2014

# ? Sep 23, 2014 16:33

esquilax: Jan 3, 2003

schmagekie posted:

You can take the column of what you're trying to grab. =VLOOKUP($A2,Data!$A:$J,COLUMN(Data!B2),0)
e: assuming the lookup range starts in col 1, otherwise you'll have to do a little thinking.

=VLOOKUP($A2,Data!$C:$J,COLUMN(Data!D2)-COLUMN(Data!$C2)+1,FALSE)

The +1 is necessary.

Or just use Index & Match.

# ? Sep 23, 2014 17:54

Ham Equity: Apr 16, 2013; The first thing we do, let's kill all the cars.; Grimey Drawer

So, I have a table with two columns of numbers. I sort by the first column first, and the second column second. I want it to sort like the above example, but it keeps sorting like the below example:

Essentially, I want it to recognize that these are series of ID numbers (some multiple sequential, others single) and put them in order. I have everything formatted as text already, is there some other funky formatting thing I have to do?

# ? Sep 25, 2014 00:10

TheLastManStanding: Jan 14, 2008; Mash Buttons!

When you sort, make sure you choose the option 'Sort numbers and numbers stored as text separately'.

Alternately, as long as your ids are always 3 and 6 digits you could just use new sorting column with:

=LEFT(A1,3)&LEFT(B1,6)

# ? Sep 25, 2014 00:33

Ham Equity: Apr 16, 2013; The first thing we do, let's kill all the cars.; Grimey Drawer

TheLastManStanding posted:

When you sort, make sure you choose the option 'Sort numbers and numbers stored as text separately'.

Alternately, as long as your ids are always 3 and 6 digits you could just use new sorting column with:

=LEFT(A1,3)&LEFT(B1,6)

Yeah, I tried sorting with both options, and the "sort separately" option comes out less hosed-up than the other one, but still looked like the above picture.

The other weird thing is that if I take one of the hyphenated numbers, paste it into an unhyphenated number's cell, then re-type the unhyphenated number, it will sort properly. If I just do a "paste formatting" it doesn't fix it, though. So, clearly there's some kind of weird formatting-but-not-formatting thing Excel is doing.

# ? Sep 25, 2014 00:44

Byers2142: May 5, 2011; Imagine I said something deep here...

Nam Taf posted:

You found one of Excel's two most beautiful function combinations. It's vlookup for cool kids.

I'm curious, what's the other beautiful function combination in your eyes?

# ? Sep 29, 2014 12:50

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

Byers2142 posted:

I'm curious, what's the other beautiful function combination in your eyes?

Sumproduct. It's mainly just one but you can use it as the foundation for a bunch of other logic and it's ridiculously powerful.

It's basically a sum() that takes a bunch of multiplied terms (hence product). This essentially translates to doing an AND() across those terms except that you can also throw in multiplication factors rather than just binary terms. So for example, say you have 3 columns - A has a shop ID (shop 1, shop 2 etc), B has a type of product (Orange, Apple) and C has the quantity sold in an individual transaction:

sumproduct((A1:A5='shop 1')*(b1:b5='apple')*(C1:C5))

will count the number of apples shop 1 sold across all transactions. Except you can just bolt in arbitrary logic in any of those. It just evaluates each set of brackets and multiplies then together. So for a row with 5 apples sold by shop 1, that resolves down to:

sumproduct((1)*(1)*(5))

Except that's done for every line and summed together.

It's sort of to sumifs what index/match is to vlookup. You can throw all kinds of stuff in the brackets and it'll take it just fine.

It basically lets you do pivottables completely manually. I'll solve about 80% of my aggregation problems with it somewhere in the mix.

# ? Sep 30, 2014 00:10

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Nam Taf posted:

Stuff about =SUMPRODUCT()

This is good knowledge, but Excel 2007 added =SUMIFS() which provides the same capability and is easier to understand.
Excel 2007 also added =COUNTIFS() and =AVERAGEIFS().

EDIT: Oops, I thought you compared it to =SUMIF(). I need to find the article suggesting =SUMPRODUCT() is more flexible to find out what it can do that =SUMIFS() or =COUNTIFS() can not.

Old James fucked around with this message at 16:21 on Sep 30, 2014

# ? Sep 30, 2014 03:57

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

Old James posted:

This is good knowledge, but Excel 2007 added =SUMIFS() which provides the same capability and is easier to understand.
Excel 2007 also added =COUNTIFS() and =AVERAGEIFS().

I still don't find it as flexible, hence my comparison of it to sumifs as like index/match to vlookup.

That may be my inexperience with the "ifs" functions though.

E: a cursory Google of "sumifs vs sumproduct" suggests that the latter is indeed more flexible, somewhat due to its implicit array nature.

Nam Taf fucked around with this message at 04:11 on Sep 30, 2014

# ? Sep 30, 2014 04:05

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

How do I count if out of all selected numbers if 3 or more of the exact numbers pop up

# ? Oct 23, 2014 22:54

ZerodotJander: Dec 29, 2004; Chinaman, explain!

What are you looking for here? The list of numbers that appear at least 3 times? The number of numbers that appear at least 3 times? The total number of occurences of numbers that appear at least 3 times? Something else?

# ? Oct 23, 2014 23:44

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

The numbers in that group that occur in that group 3 or more times, a list of the numbers that appear at least 3 times, and how many times they appear.

I thought it was pivot tables, or do I have to format the thing into a long column and group them? I'm rusty.

# ? Oct 23, 2014 23:57

ZerodotJander: Dec 29, 2004; Chinaman, explain!

By far the easiest would be to put it all in one column and then use a Pivot Table.

# ? Oct 24, 2014 00:33

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

ZerodotJander posted:

By far the easiest would be to put it all in one column and then use a Pivot Table.

Any Easier way than CTRL X?

# ? Oct 24, 2014 00:59

khazar sansculotte: May 14, 2004

Anybody have any good strategies for putting a linear trendline into a bubble chart? Doing it the naive way results in a flat trendline in the middle of my chart regardless of what data are in place. I'd like bigger bubbles to have more "gravity" in the trendline. Here's what my data looks like:

code:

The X and Y columns range over the possible values, the # column is a big countifs formula counting the number of A's (see top left) with the given X and Y values. A takes on a bunch of different values, so I'm producing a bunch of charts that I'm copy/pasting into a powerpoint presentation. All this to say: a solution to getting a trendline in a bubble chart that also automatically updates when A changes would be ideal.

# ? Oct 30, 2014 17:59

fosborb: Dec 15, 2006; Chronic Good Poster

Build your X and Y columns with a formula similar to this: =IF(C1=0,"",A1) and =IF(C1=0,"",B1)
assuming column A is your X values, B is your Y values, and C is your Volume.

e: sorry, replace the "" with NA(). so it should be =IF(C1=0,NA(),A1)
e2: and honestly, you only need to use the #N/A values for the X column. It'll ignore the rest of the values for that point after that.

fosborb fucked around with this message at 18:30 on Oct 30, 2014

# ? Oct 30, 2014 18:21

Chunjee: Oct 27, 2004

At work we typically shut down all systems around 11:30 PM but sometimes it goes past midnight into 2:00 AM. My Excel imports all of these times and now my boss wants to know the average time we shut down for the entire month/year.

If I =AVERAGE("11:55 PM","12:02 AM","12:15 AM") for example I get 8:04 AM; not exactly what I'm after.

How do I tell excel what I want? Tried putting appending the entire date but it is still confused.

Chunjee fucked around with this message at 16:17 on Nov 30, 2014

# ? Nov 30, 2014 16:12

Adbot: ADBOT LOVES YOU

# ? May 20, 2024 12:38

fosborb: Dec 15, 2006; Chronic Good Poster

Calculate shut down time as the difference between the DAY of turn on time (11/30/14 12:00:00 AM) and shut down day/time (11/31/14 2:04:13 AM).

You will get values somewhere between .95 and 1.1. Average those results. Display as time (hh:mm:ss AM/PM) to get your average shut down time.

# ? Nov 30, 2014 16:20

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

«‹›82 »