Excel

Okay . . . let's try this again.

Moderators: Shirley, Sabo, brian, rass, DaveInSeattle

User avatar
Ryan
The Dude
Posts: 10441
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

A_B wrote:Countif works, but not twice in the same cell, right?
You can probably fool it into concatenating the wins formula and the losses formula into one cell if you had to.
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
govmentchedda
The Dude
Posts: 12673
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

Yeah, I've been putting either +1 or -1.1 in a column. SUM takes care of overall units up and down. Will try the =COUNTIF. Thanks!

ETA - works perfect.
Until everything is less insane, I'm mixing weed with wine.
User avatar
govmentchedda
The Dude
Posts: 12673
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

I'm fine with a cell for wins, and a cell for losses.
Until everything is less insane, I'm mixing weed with wine.
User avatar
duff
Donny
Posts: 2745
Joined: Mon Apr 01, 2013 3:36 pm

Re: Excel

Post by duff »

I am working on a customer address list and not all of them have an address 2 line, where some do. I would like to know if there is a way to return a blank instead of a 0 when there is no information in address 2.

And then when doing a mail merge into word, I would like the blank for address 2 not to be part of the merge, but if there data in address 2 to show up.
To quote both Bruce Prichard and Tony Schiavone, "Fuck Duff Meltzer."
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Say address 1 is in cell A1 and address 2 is B1, in the cell you're trying to not return a zero, you do =IF(B2="","",B2) ... where the first "" assumes its a truly blank cell. If they're space blanks its " ", actual zeroes is either 0 or "0" depending on if its reading a number, etc ...
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
L-Jam3
The Dude
Posts: 5949
Joined: Wed Mar 13, 2013 8:43 am

Re: Excel

Post by L-Jam3 »

I'm actually doing this in Google Sheeeeeeeeeeeeeeeeeeeeeeeeets, but I'm assuming it's the same as Excel.

I'm doing a running log. I have an early goal of running 365 miles for the calendar year. I'm trying to make a formula to see how much I am ahead or behind pace, something like:

Total Miles Ran for the Year - Days elapsed in the year.

I'm trying to have the second element, days in the year, automatically adjust, so it would turn the current date into a number. January 21 becomes 21, February 3 becomes 34, etc. Any help would be worth 5 SwampBucks.
My avatar corresponds on my place in the Swamp posting list with the all-time Home Run list. Number 45 is Paul Konerko with 439.
LewP
Jackie Treehorn
Posts: 66
Joined: Wed Aug 28, 2013 7:54 pm
Location: Arlington, Texas

Re: Excel

Post by LewP »

Shirley wrote:Any of you guys know of a good, free (ideally) Monte-Carlo tool for Excel? I know I can build a simulation by hand, but I'd rather use a friendly tool.
"Never pay for the same real estate twice." General George S. Patton
LewP
Jackie Treehorn
Posts: 66
Joined: Wed Aug 28, 2013 7:54 pm
Location: Arlington, Texas

Re: Excel

Post by LewP »

degenerasian wrote:Hey!

I have a column of items. What is the formula to just add all the numbers on the left? In this example the total should be 483669
I tried to use sumif and left but I can't figure out the array because the numbers are a different number of digits.

1 USCRUDEOIL OCT 14
1 COPPER DEC 14
5 CORN DEC 14
2 LUMBER NOV 14
1 USCOFFEE DEC 14
2000000 USDCAD SPOT
2000000 USDCAD SPOT
10000 USDCAD
10000 USDCAD SPOT
10000 USDCAD SPOT
10000 USDCAD SPOT
10000 USDCAD SPOT
10000 USDCAD SPOT
10000 USDCAD SPOT
10000 USDCAD SPOT
150 EURODOLLAR DEC 14
1000 RFP (US)
1000 POT (US)
500 PGF (CAN)
1000 PGF (CAN)
1 USRUSS2000
1 USRUSS2000
1 USRUSS2000
1 USRUSS2000
2 USRUSS2000
1 USRUSS2000
1 USRUSS2000
1 USRUSS2000
Maybe this will help?

"Never pay for the same real estate twice." General George S. Patton
LewP
Jackie Treehorn
Posts: 66
Joined: Wed Aug 28, 2013 7:54 pm
Location: Arlington, Texas

Re: Excel

Post by LewP »

Jerloma wrote:I need a formula to convert a column of numbers to 60% of those numbers. I was thinking =(*.6) but it doesn't want to hear it.

Input Variables --
Sample Proportion (p-bar) 0.43 <-- Input the appropriate number for your situation
Sample Size (n) 100 <-- Input the appropriate number for your situation
Confidence Level: 0.95 <-- Input the appropriate number for your situation

Intermediate Calculations --
Standard Error of the Estimate: 0.0495
Prob. in One Tail for this Conf Level: 0.025
Prob. to use in NORMSINV: 0.975
Z Multiple: 1.960

Confidence Interval --
Lower limit: 0.3330 We can be 95% confident that the population proportion
Upper limit: 0.5270 lies between 0.333 and 0.527
Margin of error: 0.0970

"Never pay for the same real estate twice." General George S. Patton
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

What the fuck man.
You know what you need? A lyrical sucker punch to the face.
LewP
Jackie Treehorn
Posts: 66
Joined: Wed Aug 28, 2013 7:54 pm
Location: Arlington, Texas

Re: Excel

Post by LewP »

A_B wrote:What the fuck man.
OK, thats it.....I'm trying to help here and for some reason even that pisses you off...I'm done with this fucking board..see you.
"Never pay for the same real estate twice." General George S. Patton
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

I mean. rhat shit had already been handled right? Am I crazy?

Or are you all welcome?
You know what you need? A lyrical sucker punch to the face.
User avatar
L-Jam3
The Dude
Posts: 5949
Joined: Wed Mar 13, 2013 8:43 am

Re: Excel

Post by L-Jam3 »

Thanks, dicks. I could've used the Excel help.
My avatar corresponds on my place in the Swamp posting list with the all-time Home Run list. Number 45 is Paul Konerko with 439.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

I think the current date function is something like =NOW(). Subtracting that from 01-01-17 then multiplying by annual divided by 365 will give you current day expected and just subtract actual.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Rams Fanny
Bunny Lebowski
Posts: 623
Joined: Mon Mar 11, 2013 5:32 pm
Location: Henderson, NV

Re: Excel

Post by Rams Fanny »

This is from 2012 but the days match:

https://drive.google.com/previewtemplat ... ode=public
"Pain or damage don't end the world. Or despair or fucking beatings. The world ends when you're dead. Until then, you got more punishment in store. Stand it like a man... and give some back." -Al Swearengen
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

mister d wrote:I think the current date function is something like =NOW(). Subtracting that from 01-01-17 then multiplying by annual divided by 365 will give you current day expected and just subtract actual.
In excel it's TODAY() but maybe that is for sheets.

And wouldn't it be just as easy as TODAY-1/1/17(formatting the cell with the formula to numbers) then subtracting the number of miles run? Negative numbers mean ahead of schedule in that.
You know what you need? A lyrical sucker punch to the face.
User avatar
Ryan
The Dude
Posts: 10441
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

Oh my god, this was great
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
P.D.X.
The Dude
Posts: 5282
Joined: Wed Mar 13, 2013 12:31 pm

Re: Excel

Post by P.D.X. »

I don't even know the correct terminology to google soooo...

If I have a couple rows of header info, column labels, etc that I want to stay persistent at the top of the table as it's scrolled through, how do I do that... thing?
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Select where you want the freeze to take place, then go to View --> Freeze Panes.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

go to the far left and upmost cell below your headers, though.
You know what you need? A lyrical sucker punch to the face.
P.D.X.
The Dude
Posts: 5282
Joined: Wed Mar 13, 2013 12:31 pm

Re: Excel

Post by P.D.X. »

thx dudes
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel

Post by Square Rob »

The fun thing about coming back around here is seeing rando threads where AB runs fools out over stupid shit like excel. Stone cold killer.



BTW- how many y'all use a mouse? Been working heavy with MBA/Finance types in past 6 months, and have learned all the keyboard shortcuts. Amazed by how much it sped up my calculatin and tabulatin. Was shocked to learn that one of my colleagues worked in a European investment bank and they refused to even supply them with a mouse.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Excel + mouse = clown
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel

Post by Square Rob »

Sweet, imma cool kid.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Alt Tab Crew.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel

Post by Square Rob »

Ctrl Alt V, V, bitch.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Square Rob wrote:Ctrl Alt V, V, bitch.
Must be a custom keyboard for you if it has a bitch key!!!!!!!
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

I'm almost exclusively keyboard in Excel, too, though my new version certainly is trying to push the ribbons.
You know what you need? A lyrical sucker punch to the face.
User avatar
rass
The Dude
Posts: 20209
Joined: Mon Mar 18, 2013 9:41 am
Location: N effin' J

Re: Excel

Post by rass »

Need to do a replace all in a column, and change a value to 000 (that's three zeroes). The cell format for the column is already Text. I can't get Excel to not change 000 to plain 0. I've tried in a couple of different versions of Excel (server I was working on happened to have 2003 and I wanted to make sure the compatibility layer that let's that version work with XLSX files wasn't the issue).


https://www.youtube.com/watch?v=FSZ25ikUKVY
I felt aswirl with warm secretions.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Either custom number format of 000 or add an apostrophe in front of the 000.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
rass
The Dude
Posts: 20209
Joined: Mon Mar 18, 2013 9:41 am
Location: N effin' J

Re: Excel

Post by rass »

The latter was tried first and worked and thanks.
I felt aswirl with warm secretions.
User avatar
govmentchedda
The Dude
Posts: 12673
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

Any idea why, or how to fix, the issue that when I type "11/29" (without the parentheses), in some cells it shows up as "11/29/17", and in others it shows up as "29-Nov"?

ETA - either way would be fine, but the random difference is driving me nuts.
Until everything is less insane, I'm mixing weed with wine.
User avatar
Johnny Carwash
The Dude
Posts: 5952
Joined: Mon Mar 11, 2013 8:57 am
Location: Land of 10,000 Sununus

Re: Excel

Post by Johnny Carwash »

On the Home tab, there is a "Number" section with a drop-down box to where you can select the formatting options. There are a few different ones for dates. Select all the appropriate cells and apply the same format.
Fanniebug wrote: P.S. rass! Dont write me again, dude! You're in ignore list!
User avatar
govmentchedda
The Dude
Posts: 12673
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

Johnny Carwash wrote:On the Home tab, there is a "Number" section with a drop-down box to where you can select the formatting options. There are a few different ones for dates. Select all the appropriate cells and apply the same format.

Thanks
Until everything is less insane, I'm mixing weed with wine.
elflaco
Brandt
Posts: 485
Joined: Sat Mar 16, 2013 4:46 am
Location: Exit 10

Excel or??

Post by elflaco »

thought of putting this in the Tammany Soccer thread but i'll leave the rant part out for that post later..

our second session of tryouts is tonight -- even though there is a third for all teams over the next two weeks, we expect to start sending out invites out on monday...

currently researching if our website has that feature (SI Play) -- if not..

if we have a stock invite letter in word(yeay, you made it) and a stock rejection letter (sorry try again next year) --and we have a list (in excel and google docs) of names, age groups, teams and status.. how can we create 250+ letters and email them out (we plan to use the stock website email for the training/development vp)

any swamper have ideas on how to do this?
teeteebee
Brandt
Posts: 366
Joined: Sun Mar 17, 2013 6:50 am

Re: Excel

Post by teeteebee »

Ok, let me see if I can convey my issue.

Have a P&L that is built with months in each column.

Have 12 months of 2017 (actual), 12 months of 2018 (actual) with placeholder columns for months that have not occurred yet and 12 months of 2018 (plan). That’s the table. The rows are the various lines from Revenue on down.

Have QTD and YTD columns with formulas as well as better/worse MTD/QTD/YTD for actual vs PY and actual vs Plan.

My issue is that I don’t want to have to update the formulas each month to capture the appropriate cells to determine the QTD/YTD or the vs PY and vs Plan columns.

Was thinking there would be away to add hidden columns with coding so that the user can select a period (say May 2018) and the formulas will automatically calc Apr and May to get QTD plus Jan to May for YTD. Also, for the flux calcs, knowing to only take April and May for CY, PY and Plan to properly calc the deltas. Same for YTD.

Can this be done with formulas driven by selecting reporting month? Or is find/replace my best friend for this task?




Edited for typos.
oh shit...
User avatar
brian
The Dude
Posts: 27742
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

Let's say you're hypothetically dealing with a bunch of lazy Canadians.

And let's say you're providing them data in an Excel file that lists a city for each person in a column and there are 25,000 rows. (There's other data in there, but they don't care about that.)

How could you use Excel to list the number of times each city appears in the report in a graph or some other way to represent how many times each city is represented without having to compile it manually?

For example:

Toronto - 352
Oshawa - 283
Thunder Bay - 218

etc., etc.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Would building a summary section, using COUNTIF and running the graph off there count as manual?
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
brian
The Dude
Posts: 27742
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

mister d wrote: Tue Oct 16, 2018 3:57 pm Would building a summary section, using COUNTIF and running the graph off there count as manual?
No, I meant literally manual. Like all I would know how to do is sort by that column and then "tally" up the results. So anything that involves even a few steps is fine.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
Post Reply