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

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

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 15:39

TheLastManStanding: Jan 14, 2008; Mash Buttons!

It's called a map. I don't think there is a function for it, but the math is simple enough.

code:

= (input - min) * (1 - 0) / (max - min) + 0

You can replace the 1 and 0 with whatever range you want it to map too, or remove them if you're just normalizing the data.

# ¿ Feb 9, 2015 22:29

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Pivot tables can be a bit wonky if you try to do too much inside them; I'd add a second table (a regular table) just to the right, have it do the math using table headers, then hide col C-H.

# ¿ Feb 13, 2015 01:01

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Like this?

# ¿ Mar 2, 2015 03:58

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Just have column that subtracts the in time from the out time and format the cell to display only hours.

Edit: Ctrl+; and Ctrl+Shift+; with a space in between will auto add the current day+time as a fixed value, which should save time if you are having people manually type in the time when they have an order updated. For formatting it would be under custom, [h]:mm. That would report in total hours with minutes. If you want fractional hours you'll have to do math using the hour and minutes functions, something like:

code:

=INT(H9-G9)*24+HOUR(H9-G9)+MINUTE(H9-G9)/60
Format cell as number

That would return a number as opposed to an excel time code.

TheLastManStanding fucked around with this message at 21:49 on Mar 25, 2015

# ¿ Mar 25, 2015 21:32

TheLastManStanding: Jan 14, 2008; Mash Buttons!

It's not part of the table. If you click something in the table there should be an outline of the whole table which you should be able to adjust. Otherwise just select all your data and table it again. Once it is part of the same table you can toggle the drop down menus on and off by clicking the filter button in the data tab.

# ¿ May 8, 2015 05:33

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Concatenate the two into a third column and check that for dupes.

# ¿ Mar 22, 2016 01:28

TheLastManStanding: Jan 14, 2008; Mash Buttons!

I haven't used google sheets in depth enough to know if that function exists, but if all else fails you could calculate the quadratic regression yourself, setting aside a few cells hold the coefficients.

http://keisan.casio.com/exec/system/14059932254941

# ¿ Mar 23, 2016 05:42

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Copy value from every nth column

# ¿ May 28, 2019 07:55

TheLastManStanding: Jan 14, 2008; Mash Buttons!

nickhimself posted:

Here's the first screen. The '10361' is the customer number, the values highlighted below are all costs associated with this customers statement for the month.

This is what I'd like it to look like on the next sheet

Thanks so much for any help, even if it's just linking a tutorial. I might just not know how to search for this to figure it out on my own =[

Convert it to a table. Filter column A by "Customer #", copy out column B. Filter column A by "Total Due:", copy out columns B:G.

# ¿ Jun 7, 2019 22:30

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Create a column that you can hide later and put in:

code:

=NUMBERVALUE(LEFT(B2,(FIND(" ",B2,1)-1)))

That will pull out the level. Then select your level column (B2:B5 in this example), and add conditional formatting.

code:

Use a formula to determine which cells to format
Format values where this is true.
=NOT(C2-MAX($C$2:$C$5))

But really you should just break it up.

# ¿ Jun 28, 2019 03:40

TheLastManStanding: Jan 14, 2008; Mash Buttons!

LawfulWaffle posted:

1) Can a table be formatted so when a new row is added, some cells are already populated?
2) When pulling data through a query, can that data be further manipulated by a formula in the destination?

1) Cells in a table that are functions should auto populate when you add a new row. If you make the reference absolute (ie. press f4 to make the cell =$A$3) then you won't have to pre-populate the next few rows to prevent it from incrementing.
2) When making your query: Transform>Format>Uppercase.

# ¿ Apr 3, 2020 03:38

TheLastManStanding: Jan 14, 2008; Mash Buttons!

tuyop posted:

Hey Excel thread. I'm not sure which function I need to use for this thing, and I know one of my students is definitely going to ask tomorrow.

I have names in column A, and a bunch of sales figures in B:E. I'm wondering which function will give me the content of a cell in A for a given value in B:E.

They'll be using a function to find the max in the table, but the question also has the students determine the value from column A that corresponds to the cell containing the max value, which can be anywhere from B3:E7. The instruction is to just look with your eyes or maybe use ctrl+f if you're feeling fancy, but I'm sure there's some lookup and reference function for this, I'm just not sure which one.

Easiest way would be to add another column:
F3=MAX(B3:E3)

Then your result cell would be:
=INDEX(A3:A7,MATCH(MAX(F3:F7),F3:F7,0))

There are ways to this without the extra column, but they involve array formulas.

# ¿ Apr 26, 2020 22:32

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Sad Panda posted:

I'm trying to play around with my Excel markbook and decide based on 3 values which of 6 categories that leads to. The brute for way seems to be an exceedingly long IF statement. Any other advice would be appreciated.
A1, A2, A3 hold the values. They can be U, L1P, L1M, L2P, L2M, L2D.

That's not that many cases. Just concatenate the inputs, build a lookup table, and use IFNA() to handle the default case.

# ¿ Jun 19, 2020 06:07

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Sad Panda posted:

With 3 values and 6 possible options each isn't that 6^3 (216) different things to have in my lookup table? I guess I can drop it so anything that leads to a U becomes the default value, so then it's 5^3 (125) instead, but that seems rather large.

Edit - I implemented this, and it works. Just feels like there's got to be something more efficient than a hardcoded table with 125 values.

Yeah, I didn't fully parse that your + usage meant any value greater than (that whole L1PL1ML2P system has terrible readability), so I thought you were only dealing with 8 cases; though 125 is still not that many cases.
I played around in excel for a bit and came up with the formula below. It assumes input values 1 to 6 (1 being U and 6 being L2D).
It seems to work, but you'll probably want to check it against your table.

code:

=MAX(MIN(A1,A2,A3),MIN(A1,A2,A3)*NOT(MOD(MIN(A1,A2),2))+(A3>MIN(A1,A2)+1)*1)

# ¿ Jun 22, 2020 07:36

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Sad Panda posted:

One thing that jumped out, I'm not sure what *1 does? When does x *1 not = x?

(A3>MIN(A1,A2)+1) converts the cell to a boolean, so you multiply it by 1 to convert it back to a number.

One important thing to note; based on what you had wrote ("A1 + A2 are both L2P+ and A3 is L2D" ), I took that to mean that A3 is treated differently. So for example:
2, 2, 4 = 3 whereas 4, 2, 2 = 2

If that's not the case and order isn't dependent, then it simplifies to

code:

=MIN(A1,A2,A3)+(MAX(A1,A2,A3)>MIN(A1,A2,A3)+1)*ISEVEN(MIN(A1,A2,A3))

Edit:
I also realized the original code can be simplified

code:

=MIN(A1,A2,A3)+(A3>MIN(A1,A2)+1)*ISEVEN(MIN(A1,A2))

TheLastManStanding fucked around with this message at 20:50 on Jun 22, 2020

# ¿ Jun 22, 2020 20:05

TheLastManStanding: Jan 14, 2008; Mash Buttons!

tuyop posted:

I think the thing that breaks my brain about it is the MOD just in the middle there. Doing important but mysterious work. And yet, it goes away when we just skip to multiplying by whether or not the result is even. Which is not only possible but matters somehow.

NOT(MOD(X,2)) is the same as ISEVEN(X). It's there to handle the two weird cases where you can have a score higher than your lowest, which can only happen when the number is even.

# ¿ Jun 24, 2020 05:06

TheLastManStanding: Jan 14, 2008; Mash Buttons!

If you don't mind programming, then look into editing files through the component object model. You could write a script that could take an excel file, pull out the user inputted cells, insert them into your blank template, then save it out as a validated file. For tasks like this I prefer AutoHotkey, but it could be done in any language .

# ¿ Jul 3, 2020 02:34

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Secx posted:

Is there another syntax I can use to avoid hard coding the rows? My columns will always be the same when I paste my data. It's the number of rows that I can't control.

It seems to work with table syntax

code:

=SUM(IF("Jennifer"=Table2[Person], 1/COUNTIFS(Table2[Person],"Jennifer",Table2[Product],Table2[Product]),0))

Someone on that site you linked also gives a shorter variation

code:

=SUM((Table2[Person]="Jennifer")/COUNTIFS(Table2[Person],Table2[Person],Table2[Product],Table2[Product]))

There's also this site which has an example using pivot tables (scroll down to method 3).

# ¿ Nov 10, 2020 05:53

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Sad Panda posted:

This is in a table with name MainTable. What formula can I put in M5 to make it take the average of all columns to the right of it? Hard coding N5:R5 worked, until I added 'A N Other'.

Assuming you aren't going to insert a column between Average and MS:

code:

=AVERAGE([@MS]:INDEX(MainTable, ROW()-ROW(MainTable)+1, COLUMNS(MainTable)))

If you want to guarantee starting one column to the right of Average, then you have to expand it out a bit more.

code:

=AVERAGE(INDEX(MainTable, ROW()-ROW(MainTable)+1, COLUMN([Average])-COLUMN(MainTable)+2):INDEX(MainTable, ROW()-ROW(MainTable)+1, COLUMNS(MainTable)))

There is probably a slightly more elegant way to condense this.

TheLastManStanding fucked around with this message at 03:14 on Jan 3, 2021

# ¿ Jan 3, 2021 00:44

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Sad Panda posted:

I tried that and it returns #VALUE!

Shouldn't that be ROW(MainTable)+1 - ROW()?

As in get the first row of the main table, add 1 to it, and offset the current row to work out which row of the main table we are in?

INDEX doesn't seem to like having the array of ROW(MainTable) passed into it and won't just take the first value from the returned array.

If it isn't liking ROW(MainTable), then MainTable probably isn't actually the name of your table. Also, it will only work inside the table itself.

The index needs the row number of the table, so we're taking the current row number of the sheet and subtracting the row number of the first row of the table, then adding 1. Doing it the way you suggested would give an increasingly negative number. You could alternately write it as ROW() - ROW(MainTable[#Headers]).

# ¿ Jan 3, 2021 20:23

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Not sure why it's not working. Everything looks right. I've sent you a PM.

I've also come up with another solution using OFFSET.

code:

=AVERAGE(OFFSET([@Average],0,1,1,COLUMN(MainTable)+COLUMNS(MainTable)-COLUMN([Average])-1))

# ¿ Jan 3, 2021 23:18

TheLastManStanding: Jan 14, 2008; Mash Buttons!

- You could use IFNA or ISBLANK to specify a default response for each given lookup.
- INDEX would be better than VLOOKUP since you are just returning an item from a list.
- Avoid using random, it regenerates any time you modify any cell, which lowers performance and will look silly when your comments are constantly switching. Try coming up with some deterministic way you pick which comment is returned.

If you make a table for each class of comments then you could have the code adjust any time you add or subtract comments by using:
INDEX(CommentTable,RANDBETWEEN(1,ROWS(CommentTable)))
or, without the actual random element:
INDEX(CommentTable,MOD(ROW(),ROWS(CommentTable))+1)

# ¿ Mar 13, 2021 23:44

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Does it really need to be random though? In the example I posted I was using ROW() to cycle through the choices. If you are picking from 3 different lists then the result should look sufficiently random. If it still looks too much like a pattern then you could built a random number table and use that. Or use the contents of another cell in the row as a random number. There are plenty ways of avoiding using rand.

# ¿ Mar 14, 2021 22:53

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Data Tab > New Query > From File > From Workbook
Pull in your master data table.
Filter the column for Day4.

This would give you all of the product/dates for Day4.
They could then filter this down to the products they want.

If you really want a table where they plug in combinations and it gives a result then:
For table 1 (your master table) add a column AB that is
=[@A]&[@B]
Then for column C in table 2 (your new table) use
=INDEX(Table1[C],MATCH([@A]&[@B],Table1[AB],0))

# ¿ Jun 25, 2021 03:38

TheLastManStanding: Jan 14, 2008; Mash Buttons!

simplefish posted:

The conditional formatting is set up as cell value = 0 is blue, <0 red, >0 green, applied to $F:$F.
Red and green work fine, even blue works fine if I type in 0 or 0:00 directly to a cell.
But when it's an output the cell is red - using the example above, the cell shows +0:00 but is filled red.

Works for me? Tried a couple different ways and they all worked.

code:

Conditional Format applied to F:F
=IF(IF($F1="",1,$F1)=0,1,0)
=AND(IF($F1=0,1,0),IF($F1="",0,1))
=AND(NOT($F1),NOT($F1=""))
=NOT(OR($F1,$F1=""))

I thought it could be a floating point error, but I tried a summing a column of values and it worked every time.
When its at 0:00 and you change the format back to a number, what happens?

# ¿ Dec 9, 2021 07:38

TheLastManStanding: Jan 14, 2008; Mash Buttons!

kumba posted:

code:
Row # | Name | Date   | AbsenceType | DoesItCount?
1     | Bill | 1/3/22 | Call Out    | Yes
2     | Jeff | 1/3/22 | Call Out    | Yes
3     | Gina | 1/4/22 | Call Out    | Yes
4     | Bill | 1/4/22 | Call Out    | No
5     | Bill | 1/5/22 | Call Out    | No
6     | Jeff | 1/5/22 | Call Out    | Yes
My thought was to use a helper worksheet with a calendar full of all the workdays and assign consecutive numbers to each (we're not open for business every day so I can't just compare the dates themselves), and use this to identify gaps larger than 1, but how to shoehorn this in to this workbook is really evading me.

Anybody done anything like this and got any tips??

Make your table of workdays and use VLookup to convert your dates to numbers (in this example I named the column 'DateNumber'. Then make a column to concat the name and date number (I called this 'NameDate').

code:

=[@Name]&[@DateNumber]

Then for each absence you can assign it a 1 or 0 based on if an absence exists for that person on the previous date number.

code:

=IF(IFNA(MATCH([@Name]&([@DateNumber]-1),[NameDate],0),0),0,1)
or
=IF(COUNTIF([NameDate],[@Name]&([@DateNumber]-1)),0,1)

# ¿ Dec 30, 2021 00:16

TheLastManStanding: Jan 14, 2008; Mash Buttons!

HootTheOwl posted:

=D1-B1+(24*(C1-A1))
End time minus start time, plus 24 for every day across the range
Then format the cell

This wouldn't work as D1-B1 would return the difference as a decimal day, and then you are adding the day difference in hours.

It should just be either of the below:
=(D2+C2-B2-A2) [Format as Time - Displays as Hours:Minutes]
=24*(D2+C2-B2-A2) [Format as Number - Displays as decimal hours]

# ¿ Nov 28, 2023 05:32

TheLastManStanding: Jan 14, 2008; Mash Buttons!

HootTheOwl posted:

I tested it before I posted it, the times were already split out

It comes up with the right answer if the days are the same, but if it spans multiple days it won't.

# ¿ Nov 28, 2023 06:42

TheLastManStanding: Jan 14, 2008; Mash Buttons!

HootTheOwl posted:

Again, I tested it before I posted it.
I had two test cases, the first was the example as given, the second was from 11pm to 1am the next day.

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: Jan 14, 2008; Mash Buttons!

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.

here it is in my excel.
This behavior matches This website. Where basic subtraction is equivalent to executing the DAYS function.

It isn't evaluating to 24, I was multiplying the equation out for clarity, but maybe that was confusing. It's evaluated as 0.04-0.96+(24*(45253-45252)), which is 23.08333333, which is 554 hours, which is wrong. It should be 0.08, which is 2 hours.
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

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 15:39

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Count Thrashula posted:

I have a dumb niggling issue I can't figure out.

If you have excel through Office365 you can use TEXTSPLIT. I added the below formula to a second column in the table of test strings that returns true or false by counting the items in the string and comparing it to the count of items in the string that match any item in the lookup.

code:

=COUNTA(TEXTSPLIT([@[Test String]],","))=SUM(SIGN(COUNTIF(LookupTable[LookupColumn],TEXTSPLIT([@[Test String]],","))))

The SIGN(...) is to deal with duplicates in the lookup table.

# ¿ Mar 20, 2024 09:46

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