Excel

Okay . . . let's try this again.

Moderators: Shirley, Sabo, brian, rass, DaveInSeattle

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

Re: Excel

Post by Ryan »

Am I missing something or is there no easy way to copy and paste formula-based values without the cell reference changing? (Assuming there's no dollar signs in the original formula)
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
A_B
The Dude
Posts: 23319
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 »

The dollar signs are what keep the cell reference from changing, so no, I don't think you're missing anything.
You know what you need? A lyrical sucker punch to the face.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

However, if you don't use F4 keyboard shortcut to toggle through absolute v. relative references, you are doing it wrong.
User avatar
A_B
The Dude
Posts: 23319
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 »

wlu_lax6 wrote:However, if you don't use F4 keyboard shortcut to toggle through absolute v. relative references, you are doing it wrong.
True story. Much easier than typing them in.
You know what you need? A lyrical sucker punch to the face.
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

Huh, I never knew that one. But still, I've got a huge column of formulas. I have to insert the $ in all 500 cells manually?
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
A_B
The Dude
Posts: 23319
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 »

Ryan wrote:Huh, I never knew that one. But still, I've got a huge column of formulas. I have to insert the $ in all 500 cells manually?
I guess I don't understand...typically if you have something like that you only need the column or row to be static. So you just put the $ in front of whichever you wish to stay the same in one cell then copy it to the others.
You know what you need? A lyrical sucker punch to the face.
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

AB_skin_test wrote:
Ryan wrote:Huh, I never knew that one. But still, I've got a huge column of formulas. I have to insert the $ in all 500 cells manually?
I guess I don't understand...typically if you have something like that you only need the column or row to be static. So you just put the $ in front of whichever you wish to stay the same in one cell then copy it to the others.
It's hard to explain. The upshot is I found a shitty yet effective plug in that converted a whole column of references at the same time.
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

What goddamn motherfucking formatting do I need to pick to get these afternoon times out of 24-hour format when I drag-copy down?

Image
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

Custom and then Time (picking the specific format you want) doesn't work?
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

These are my options on Time (and I don't want "AM" and "PM" to show up)

Image
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

Ryan wrote:These are my options on Time (and I don't want "AM" and "PM" to show up)

Image
Huh, that's crazy. I tried Custom>Custom and got nothing there too. Can you just convert that way and then find and replace out AM and PM? Shouldn't take but an extra few seconds?
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

Shit, even that doesn't work.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

That's what I'll have to do. Look at this shit:

http://www.mrexcel.com/forum/excel-ques ... -pm-2.html
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
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 »

A few days late, but I'll throw up a potential challenge to J-Lo's Facebook post:
It's 2016 people. Wrap-text your damn spreadsheets. It literally takes 3 clicks!
Are you allowing for any exceptions at all? Because it's annoying as shit when someone puts a ton of text in a narrow column, then turns on Word Wrap and makes each row take up half the height of your screen. If I have a column that tends to include long text dumps (i.e. "Comments" or something like that), I make it the last column and leave it unwrapped.
Fanniebug wrote: P.S. rass! Dont write me again, dude! You're in ignore list!
User avatar
rass
The Dude
Posts: 20209
Joined: Mon Mar 18, 2013 9:41 am
Location: N effin' J

Re: Excel

Post by rass »

Johnny Carwash wrote:A few days late, but...shit...I...tend...to...leave it unwrapped.
I felt aswirl with warm secretions.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Johnny Carwash wrote:A few days late, but I'll throw up a potential challenge to J-Lo's Facebook post:
It's 2016 people. Wrap-text your damn spreadsheets. It literally takes 3 clicks!
Are you allowing for any exceptions at all? Because it's annoying as shit when someone puts a ton of text in a narrow column, then turns on Word Wrap and makes each row take up half the height of your screen. If I have a column that tends to include long text dumps (i.e. "Comments" or something like that), I make it the last column and leave it unwrapped.
Yeah, I'm with you. If I have to choose a universal method here, its (1) stop putting 1,000 characters of text inside excel, (2) fit columns then (3) wrap all.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

Okay this is google spreadsheet but pretty useful
Automatically Scrape and Import a Table in Google Spreadsheets ) -- =ImportHtml("URL", "table", num) where "table" is the element name ("table" or a list tag), and num is the number of the element in case there are multiple on the page. Bam!

oh and yes Excel has done this for a long time too...probably a bit smarter in terms of UI
http://probiztechnology.com/blog/2012/e ... nto-excel/" onclick="window.open(this.href);return false;
User avatar
Jerloma
The Dude
Posts: 7050
Joined: Mon Mar 11, 2013 2:10 pm

Re: Excel

Post by Jerloma »

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.
And the unicorns shall come down with them, and the bullocks with the bulls; and their land shall be soaked with blood, and their dust made fat with fatness. - God
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

Did you forget to reference the cell you're working off?

ETA: Or trying to overwrite into the same cell?
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

If that's not working, you don't have numbers in the original column.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Either =trim(A1) or =A1+1-1 should make it numerical.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23319
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 »

Yeah, you have to put the new calculation in a different column and reference the number in the formula, unless that column is already a formula based on a different calculation, then you could add the x.6 into the formula
You know what you need? A lyrical sucker punch to the face.
User avatar
Jerloma
The Dude
Posts: 7050
Joined: Mon Mar 11, 2013 2:10 pm

Re: Excel

Post by Jerloma »

Ryan wrote:Did you forget to reference the cell you're working off?
Ummm...yes. Wow. Carry on.
And the unicorns shall come down with them, and the bullocks with the bulls; and their land shall be soaked with blood, and their dust made fat with fatness. - God
User avatar
Jerloma
The Dude
Posts: 7050
Joined: Mon Mar 11, 2013 2:10 pm

Re: Excel

Post by Jerloma »

Could someone smarter than me tell me how I would go about formulating a spreadsheet for pro-rata calculations?

So let's say that someone wanted to take a $5000 distribution and they have 6 mutual funds and they want it allocated according to how their total account value is currently weighted. Is there any way to do that simply by plugging in the total distribution amount and the current value of each fund and hitting enter?
And the unicorns shall come down with them, and the bullocks with the bulls; and their land shall be soaked with blood, and their dust made fat with fatness. - God
User avatar
A_B
The Dude
Posts: 23319
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, if you have the balances of each of the current funds, you can absolutely calculate the percentages of the total that each makes up then multiply that by 5000, but I feel like I am missing something in your question.
You know what you need? A lyrical sucker punch to the face.
User avatar
Jerloma
The Dude
Posts: 7050
Joined: Mon Mar 11, 2013 2:10 pm

Re: Excel

Post by Jerloma »

Can I do that all within one cell? It's time sensitive so I don't want to have to make multiple calculations.
And the unicorns shall come down with them, and the bullocks with the bulls; and their land shall be soaked with blood, and their dust made fat with fatness. - God
User avatar
A_B
The Dude
Posts: 23319
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 »

Jerloma wrote:Can I do that all within one cell? It's time sensitive so I don't want to have to make multiple calculations.

I mean, you can't get the distribution amounts all in one cell but you can put the formula in separate cells five times I guess.

=5000*((Fund A Amount)/(Sum(FUND A, FUND B, FUND C, FUND D, FUND E)))

That would give you the allocation of the 5000 for fund A. You'd Have to change it to the value for Fund B, C...
You know what you need? A lyrical sucker punch to the face.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

You forgot about Fund F. J-Lo has six (6) funds.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23319
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:You forgot about Fund F. J-Lo has six (6) funds.
Oh shit. Well I'm out, then.
You know what you need? A lyrical sucker punch to the face.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

You failed him in his time sensitive matter.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Jerloma
The Dude
Posts: 7050
Joined: Mon Mar 11, 2013 2:10 pm

Re: Excel

Post by Jerloma »

This in itself is not time sensitive. The utilization of it would be. And AB is clearly still in the running to make top 10 of 2016.
And the unicorns shall come down with them, and the bullocks with the bulls; and their land shall be soaked with blood, and their dust made fat with fatness. - God
User avatar
A_B
The Dude
Posts: 23319
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 »

Jerloma wrote:This in itself is not time sensitive. The utilization of it would be. And AB is clearly still in the running to make top 10 of 2016.

Duh.
You know what you need? A lyrical sucker punch to the face.
User avatar
A_B
The Dude
Posts: 23319
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 »

Image
You know what you need? A lyrical sucker punch to the face.
User avatar
Shirley
The Dude
Posts: 7516
Joined: Mon Mar 11, 2013 2:32 pm

Re: Excel

Post by Shirley »

That's how I feel every time I see my wife drag her moust up to the menu and click Edit->Copy. Then, she does it again Edit->Paste.

Fucking Ctrl-C and Ctrl-V! It's been those keys for over 30 years!

(well, whatever that key's called on the Mac. Command?)
Totally Kafkaesque
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

Keeping a spreadsheet on betting picks. I've sorted out how to SUM from one column that has $$ won or lost, to show overall money up or down. Is there a way to use the same column and count how many positives there are and how many negatives there are to keep a W-L record? Or do I have to do a separate 2 columns that reflect W or L?
Until everything is less insane, I'm mixing weed with wine.
User avatar
A_B
The Dude
Posts: 23319
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 »

Not sure how to do it in one column. And why is putting it in two columns bad?
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 »

A_B wrote:Not sure how to do it in one column. And why is putting it in two columns bad?
Are you guys talking about two different things?

I think chedda just meant he didn't want to list his wins in one column and losses in another on a transaction by transaction basis. I think a two separate cells for a total # of wins and total # of losses would be OK?

Or not. Why the fuck am I butting in anyway?
I felt aswirl with warm secretions.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

govmentchedda wrote:Keeping a spreadsheet on betting picks. I've sorted out how to SUM from one column that has $$ won or lost, to show overall money up or down. Is there a way to use the same column and count how many positives there are and how many negatives there are to keep a W-L record? Or do I have to do a separate 2 columns that reflect W or L?
The countif formula is your trick. This page should give you some basic ideas on how to do it. I can also show you how to create dynamic ranges so you don't have to update the formula again and again as you add rows, but let's get through countif first as that dynamic range thing is Advanced Excel Kung Fu.
https://www.extendoffice.com/documents/ ... mbers.html
Last edited by wlu_lax6 on Mon Oct 17, 2016 10:36 am, edited 1 time in total.
User avatar
Ryan
The Dude
Posts: 10438
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

If I'm reading the question correctly, you can use the COUNTIF function. Assuming your losses are shown as negative values...

In whatever cell you want to show "wins", type =COUNTIF
Then, hit the function button (fx, right next to the text entry bar) to bring up the little wizard box that guides you through the formula
The range will be your column of amounts
The criteria will be <1

Repeat it in another cell and use >1 for your "losses"
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
A_B
The Dude
Posts: 23319
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 »

Countif works, but not twice in the same cell, right?
You know what you need? A lyrical sucker punch to the face.
Post Reply