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 »

Chunjee: Oct 27, 2004

I don't have the current days start time on the same line and I'm not ~~smart~~ motivated enough to calculate it off the line above. Instead I kept googling and found =AVERAGE(IF(A1:A30<0.5,A1:A30+1,A1:A30)) which seems to work.

# ? Nov 30, 2014 18:40

Adbot: ADBOT LOVES YOU

# ? May 23, 2024 20:05

Ragingsheep: Nov 7, 2009

Is it possible to have frozen rows that "stack up" as you scroll down a sheet?

i.e. I want to freeze rows 1, 100 and 200 so that if I'm scrolling from row 1 downwards, only the first row is frozen but once I go past row 100, it's frozen underneath row 1 and once I go past row 200, I have 3 rows frozen at top.

# ? Dec 1, 2014 23:58

schmagekie: Dec 2, 2003

Ragingsheep posted:

Is it possible to have frozen rows that "stack up" as you scroll down a sheet?

i.e. I want to freeze rows 1, 100 and 200 so that if I'm scrolling from row 1 downwards, only the first row is frozen but once I go past row 100, it's frozen underneath row 1 and once I go past row 200, I have 3 rows frozen at top.

Not really. See http://www.cpearson.com/excel/DetectScroll.htm
You could create a class to handle selection change events, but that would involve changing the active cell by clicking/arrow keys, or page down.

# ? Dec 2, 2014 00:16

Total Meatlove: Jan 28, 2007; Rangers died, shoujo Hitler cried ;_;

I'm trying to sum together some times in a pivot chart so I can track the number of hours given over to separate issues.

code:


A           B                 C                    D                  E                 F
Problem     Date Started      Time Started         Date Resolved      Time Resolved     Total Time
BATTERY     02/12/2014         09:00                02/12/2014         09:30             00:30
NETWORK     19/11/2014         13:54                20/11/2014         09:00             19:06

Now the formula for the Total Time column is =(CONCATENATE(B2+C2)-CONCATENATE(D2+E2)) which gives me the right amount of hours, displayed as HH:MM. I'm using the 1904 format for time if that makes any difference.

I can't for the life of me figure out how to get that into the pivot and display it properly. At the moment, I'm trying to use the two fields of Problem in the axis category, and Sum of Problem Time formatted as Time in the Values field, but the graph it creates loops around on the Y axis.

# ? Dec 2, 2014 15:39

fosborb: Dec 15, 2006; Chronic Good Poster

Have you tried wrapping your concat functions in a format function? might help the pivot table. so:
= format(concat()-concat(), "[hh]:mm")

sorry, ignore the above, doesn't work.

Is it showing like .020833 in the pivot? if so, right click the total time field in the pivot and select Value Field Settings, then Number Format. Select Custom, then add the [h]:mm.

fosborb fucked around with this message at 18:41 on Dec 2, 2014

# ? Dec 2, 2014 18:30

Total Meatlove: Jan 28, 2007; Rangers died, shoujo Hitler cried ;_;

For some reason it wouldn't work with [h]:mm but hh:mm:ss seems to have fixed it. Which is more annoying.

Thanks for your help man

# ? Dec 3, 2014 10:08

Vaz: Feb 15, 2002; Vurt Refugee

In Win7 Excel 2013 works as always have been for decades. Now on Win8.1 it works bit differently and driving me nuts. I can't figure out where in bowels of both Excel and Windows settings to set.

Windows 7 the decimal is a full stop as in 123.45
Win8.1 it is comma as in 123,45

The parameter list separator is comma as in =func(p1, p2, ....)
Window 8.1 it's semicolon! As in =func(p1; p2; .....)

So how do I set Excel on 8.1 to behave as before?

# ? Dec 21, 2014 07:24

Ragingsheep: Nov 7, 2009

Vaz posted:

In Win7 Excel 2013 works as always have been for decades. Now on Win8.1 it works bit differently and driving me nuts. I can't figure out where in bowels of both Excel and Windows settings to set.

Windows 7 the decimal is a full stop as in 123.45
Win8.1 it is comma as in 123,45

The parameter list separator is comma as in =func(p1, p2, ....)
Window 8.1 it's semicolon! As in =func(p1; p2; .....)

So how do I set Excel on 8.1 to behave as before?

Have you checked that your windows regional settings isn't set to some weird rear end location?

# ? Dec 22, 2014 00:08

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

A weird rear end location like parts of Canada or Europe or probably most of the world except the US?

Those settings can be inherited from system settings or overridden inside Excel (in 2007 it's under "Advanced"->"Use system separators").

# ? Dec 22, 2014 01:58

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

This is for Google Sheets specifically but I figured the solution might be similar in Excel so I figured I'd ask.

I have a sheet with two specific columns I'm trying work with. Column A is date, Column F is calories

I want to get a "moving average". Cell F2 should be the average using the range of the most recent (using column A) calorie entry, and up to the previous 30 entries. There may be blanks in that range that need excluded. There may be cases where there are fewer than 30 entries.

Uziel fucked around with this message at 23:10 on Dec 28, 2014

# ? Dec 28, 2014 21:47

Chernobyl Prize: Sep 22, 2006

Uziel posted:

This is for Google Sheets specifically but I figured the solution might be similar in Excel so I figured I'd ask.

I have a sheet with two specific columns I'm trying work with. Column A is date, Column F is calories

I want to get a "moving average". Cell F2 should be the average using the range of the most recent (using column A) calorie entry, and up to the previous 30 entries. There may be blanks in that range that need excluded. There may be cases where there are fewer than 30 entries.

You could put this below your column F data and reference the value in F2: =AVERAGE(F54:INDEX(F1:F54, LARGE(ROW(F1:F54)*(F1:F54<>""),30)))

That way it'll adjust as you insert rows for new dates. You'll need to ctrl-shift-enter that formula and you'll have to get rid of the letters in column F and just have null values for skipped days.

Edit: I just noticed you said there may be times when you want less than 30 days, how do you need that to work?

Chernobyl Prize fucked around with this message at 00:07 on Dec 29, 2014

# ? Dec 29, 2014 00:00

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

Chernobyl Prize posted:

You could put this below your column F data and reference the value in F2: =AVERAGE(F54:INDEX(F1:F54, LARGE(ROW(F1:F54)*(F1:F54<>""),30)))

That way it'll adjust as you insert rows for new dates. You'll need to ctrl-shift-enter that formula and you'll have to get rid of the letters in column F and just have null values for skipped days.

Edit: I just noticed you said there may be times when you want less than 30 days, how do you need that to work?

This was actually much more simple, I just don't know Excel/Sheets as well as I thought.
The solution is:
=AVERAGEIFS(F3:F, A3:A,">"&TODAY()-30, F3:F, ">0")

Uziel fucked around with this message at 02:02 on Dec 29, 2014

# ? Dec 29, 2014 01:17

Kwilty: May 31, 2011

I am attempting to throw together a code for my work that tracks sign in and sign out of jobs through a job traveler

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCheck As Range
Dim r As Range

Set rng = Range("E:E")
Set rCheck = Intersect(Target, rng)
If Not rCheck Is Nothing Then
For Each r In rCheck
If r.Value <> "" Then
r.Offset(0, 1).Value = Date
r.Offset(0, 2).Value = Time
r.Offset(0, 3).Select
End If
Next r
End If
End Sub

I've managed to get this up and running, however I also want to have the second initial record the time once they insert something into that box as well.

Any help would be greatly appreciated

# ? Dec 29, 2014 21:59

HeyMrDeadMan: Mar 10, 2007; is a swell guy

Not sure if this is actually doable in Excel. I want to be able to tally and consolidate lists of redundant data. For example, if I dropped into a table:

code:

Apples 2
Oranges 3
Bananas 2
Apples 4
Apples 1
Bananas 1
Oranges 2
Apples 1

I'd like to be able to get back out:

code:

Apples 8
Oranges 5
Bananas 3

Any ideas?

# ? Dec 30, 2014 02:55

fosborb: Dec 15, 2006; Chronic Good Poster

HeyMrDeadMan posted:

Not sure if this is actually doable in Excel. I want to be able to tally and consolidate lists of redundant data. For example, if I dropped into a table:
code:
Apples 2
Oranges 3
Bananas 2
Apples 4
Apples 1
Bananas 1
Oranges 2
Apples 1
I'd like to be able to get back out:
code:
Apples 8
Oranges 5
Bananas 3
Any ideas?

Use a PivotTable

# ? Dec 30, 2014 03:02

HeyMrDeadMan: Mar 10, 2007; is a swell guy

fosborb posted:

Use a PivotTable

If it is actually that easy, I am going to be incredibly disappointed in myself.

Edit: Mother fucker... It was actually that easy...

HeyMrDeadMan fucked around with this message at 04:11 on Dec 30, 2014

# ? Dec 30, 2014 04:01

Turkeybone: Dec 9, 2006

You could've also used SUMIF or SUMIFS in that case.

# ? Jan 22, 2015 19:21

KernelSlanders: May 27, 2013; Rogue operating systems on occasion spread lies and rumors about me.

How do you array formulas on mac?

# ? Jan 25, 2015 05:49

Turmoil: Jun 27, 2000; Forum Veteran; Young Urchin

I have a piece of code to determine how many columns I need for another part of the code.
I need to round up to the next highest integer. So if the count is 2.1, then I need to round it up to 3.

Right now I have it doing this, but I was wondering if there is a better way to do it.

Thanks!

code:

Dim LastRow As Integer
LastRow = Range("A1").End(xlDown).Row


Dim ColumnCount As Integer
If Round(LastRow / 45, 0) < LastRow / 45 Then
ColumnCount = Round(LastRow / 45, 0) + 1
ElseIf Round(LastRow / 45, 0) >= LastRow / 45 Then
ColumnCount = Round(LastRow / 45, 0)
End If

Turmoil fucked around with this message at 15:14 on Jan 25, 2015

# ? Jan 25, 2015 14:16

fosborb: Dec 15, 2006; Chronic Good Poster

Turmoil posted:

I have a piece of code to determine how many columns I need for another part of the code.
I need to round up to the next highest integer. So if the count is 2.1, then I need to round it up to 3.

Right now I have it doing this, but I was wondering if there is a better way to do it.

Thanks!
code:
Dim LastRow As Integer
LastRow = Range("A1").End(xlDown).Row


Dim ColumnCount As Integer
If Round(LastRow / 45, 0) < LastRow / 45 Then
ColumnCount = Round(LastRow / 45, 0) + 1
ElseIf Round(LastRow / 45, 0) >= LastRow / 45 Then
ColumnCount = Round(LastRow / 45, 0)
End If

=ceiling(2.1,1) will return 3

# ? Jan 25, 2015 16:28

esquilax: Jan 3, 2003

So I have a semi-advanced question.

We've been maintaining an .xlsm workbook, and some of the tabs in the worksheet are "Very Hidden". That is, we set the tab properties in VBA to have visibility 2. Users can pull data from a "very hidden" sheet, and it's completely unencrypted, but you can't actually unhide the sheet and see it. There is a password on the VBA project (so you can't unhide via VBA), but the workbook is otherwise unprotected.

The person who maintained the sheet left the company, and he was the only one who actually knew the VBA password.

Is there a way to unhide these sheets and/or crack the VBA password to make the workbook useful again? I have a feeling it's possible since the contents of the hidden sheets are completely unencrypted.

# ? Feb 4, 2015 22:52

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Unless I did it wrong just now, can't you just open a new workbook, and run code in the new workbook to unhide the sheet in your first workbook? It seems to work fine.

Otherwise there's probably a way to break the password. I know that for Excel sheet passwords (not VBA projects) in .xlsx/.xlsm files, all you need to do is open up the OOXML that defines the sheet and remove a line that says that there is a password (I forget the details). It's probably more complicated to break project passwords (the VBA code and settings is all packaged in a binary file iirc).

# ? Feb 5, 2015 01:08

FAN OF NICKELBACK: Apr 9, 2002

Make a backup of the workbook then rename the new file to a .zip extension. Open it up with 7-zip and edit the workbook.xml in the xl folder and/or the worksheets in the xl/worksheets folder depending on what you need to accomplish.

Don't extract the folders or anything--Just dig through the zip and edit the individual files, saving them back to the archive when done. Rename it back to the original extension when finished, and if you get an error when you try to open it, just make a new back up and try again.

In workbook.xml delete whatever hidden states you need removed, like:

state="hidden"

from lines that look like:

<sheet name="Sheet2" sheetId="2" state="hidden" r:id="rId2"/>

You can delete sheet protections by going into that particular worksheet's xml in the worksheets folder. Look for a line that says something like:

<sheetProtection password="CF45" sheet="1" objects="1" scenarios="1"/>

and delete it.

This also works for other office stuff like Word docs.

Just another reason not to put anything private into an excel file that you'll be sharing.

# ? Feb 5, 2015 08:44

Ragingsheep: Nov 7, 2009

Is there actually any way of protecting sensitive information in an excel workbook?

# ? Feb 5, 2015 09:02

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.

Ragingsheep posted:

Is there actually any way of protecting sensitive information in an excel workbook?

Yes, keep the exel workbook behind the trust barrier. Otherwise I wouldn't rely on any of them to be more than a minor annoyance.

# ? Feb 5, 2015 09:15

esquilax: Jan 3, 2003

ShimaTetsuo posted:

Unless I did it wrong just now, can't you just open a new workbook, and run code in the new workbook to unhide the sheet in your first workbook? It seems to work fine.

Yeah this worked. Thanks!

# ? Feb 5, 2015 14:04

distortion park: Apr 25, 2011

Ragingsheep posted:

Is there actually any way of protecting sensitive information in an excel workbook?

No, but that doesn't stop people from trying

# ? Feb 5, 2015 23:05

TheEffect: Aug 12, 2013

Is there any way to post a single column from Excel into an existing table in OneNote as an additional column? Every time I try it ends up just nesting the column inside the other column.

# ? Feb 6, 2015 21:09

C-Euro: Mar 20, 2010; Soiled Meat

Is there a function to normalize a data set between 0 and 1, and if so what is it? Normally I just normalize by dividing everything in the set by the highest value therein, but I'm curious to see what it looks like if the lowest value is 0 as well.

# ? Feb 9, 2015 21:20

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

C-Euro: Mar 20, 2010; Soiled Meat

Oh duh, that seems kind of obvious now that I see it. Thanks!

# ? Feb 9, 2015 22:59

TheEffect: Aug 12, 2013

TheEffect posted:

Is there any way to post a single column from Excel into an existing table in OneNote as an additional column? Every time I try it ends up just nesting the column inside the other column.

What I ended up doing was exporting the table from OneNote to Excel, adding the column there, and importing it back into OneNote.

pointsofdata posted:

No, but that doesn't stop people from trying

Really? I thought the new version was pretty much hack proof, aside from like brute force?

# ? Feb 11, 2015 15:57

distortion park: Apr 25, 2011

TheEffect posted:

What I ended up doing was exporting the table from OneNote to Excel, adding the column there, and importing it back into OneNote.

Really? I thought the new version was pretty much hack proof, aside from like brute force?

Unless I'm badly mistaken you can only encrypt the whole workbook securely, not individual elements

# ? Feb 13, 2015 00:22

greazeball: Feb 4, 2003

I've got the following pivot table going:

Column A is people's level of English, Column B is the number of respondents at that level. The remaining columns are the number of people who read these different types of things. Is there a way to have columns C-H display as a percentage of column B?

# ? Feb 13, 2015 00:48

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

fosborb: Dec 15, 2006; Chronic Good Poster

greazeball posted:

I've got the following pivot table going:

Column A is people's level of English, Column B is the number of respondents at that level. The remaining columns are the number of people who read these different types of things. Is there a way to have columns C-H display as a percentage of column B?

the answer is yes but it depends on how your underlying data is structured. It looks like each column is it's own value, so you can create a calculated field for each column (it's up top on the pivot ribbon) that is [normal books read] / [number at level], etc.

# ? Feb 13, 2015 01:33

greazeball: Feb 4, 2003

Thanks you guys! I'd like to try your method fosborb, but "number at level" is just a count of the row labels, so I don't seem to be able to use that. If I had that as a field in my data, then I could, right? I've still got a lot to learn but my wife will be around this weekend and she's much better at Excel so I'll get a tutorial from her.

For now, TheLastManStanding's method is working though, thanks!

# ? Feb 13, 2015 11:22

fosborb: Dec 15, 2006; Chronic Good Poster

no worries! Like I said it really depends on how your data is originally structured. If you're still interested in containing this within your pivot table alone (and you absolutely should be able to do that if you want) then just posting a single row from your data set would be enough.

Otherwise, I hope you enjoy a very data driven Valentine's Day weekend with your wife!

# ? Feb 13, 2015 14:11

Papercut: Aug 24, 2005

e: durr, nevermind. Realized how dumb I was being.

Papercut fucked around with this message at 21:41 on Feb 17, 2015

# ? Feb 17, 2015 21:03

Adbot: ADBOT LOVES YOU

# ? May 23, 2024 20:05

TheEffect: Aug 12, 2013

I have two Excel documents that I need to combine. One has multiple sheets and 3 columns in each. The second one has the same three columns, one sheet only. I need something that will look at the first column in document B and search for a match in document A, and when it finds a match it will copy the next two columns associated with that row to the other workbook.

I imagine there's not a simple elegant way to do this is there?

Edit- something that doesn't cost 50 bucks- https://www.ablebits.com/excel-lookup-tables/

Although goddamn does that work marvelously. I still need something for when the free trial expires though. If there's not a simple solution I might just bite the bullet and buy it because the thing is pretty drat great honestly.

TheEffect fucked around with this message at 17:11 on Feb 19, 2015

# ? Feb 19, 2015 16:34

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

«‹›82 »