Excel

Ryan · Post by **Ryan** » Thu Apr 23, 2015 11:34 am

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)

A_B · Post by **A_B** » Thu Apr 23, 2015 11:38 am

The dollar signs are what keep the cell reference from changing, so no, I don't think you're missing anything.

wlu_lax6 · Post by **wlu_lax6** » Thu Apr 23, 2015 11:50 am

However, if you don't use F4 keyboard shortcut to toggle through absolute v. relative references, you are doing it wrong.

A_B · Post by **A_B** » Thu Apr 23, 2015 11:58 am

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.

Ryan · Post by **Ryan** » Thu Apr 23, 2015 12:09 pm

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?

A_B · Post by **A_B** » Thu Apr 23, 2015 12:14 pm

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.

Ryan · Post by **Ryan** » Thu Apr 23, 2015 12:19 pm

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.

Ryan · Post by **Ryan** » Thu Jun 04, 2015 11:48 am

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

Post by **brian** » Thu Jun 04, 2015 11:57 am

Custom and then Time (picking the specific format you want) doesn't work?

Ryan · Post by **Ryan** » Thu Jun 04, 2015 12:03 pm

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

Post by **brian** » Thu Jun 04, 2015 12:07 pm

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

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?

Post by **brian** » Thu Jun 04, 2015 12:07 pm

Shit, even that doesn't work.

Ryan · Post by **Ryan** » Thu Jun 04, 2015 12:08 pm

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

http://www.mrexcel.com/forum/excel-ques ... -pm-2.html

Johnny Carwash · Post by **Johnny Carwash** » Tue Jan 12, 2016 10:53 am

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.

rass · Post by **rass** » Tue Jan 12, 2016 11:02 am

Johnny Carwash wrote:A few days late, but...shit...I...tend...to...leave it unwrapped.

mister d · Post by **mister d** » Tue Jan 12, 2016 11:11 am

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.

wlu_lax6 · Post by **wlu_lax6** » Wed Feb 24, 2016 7:57 am

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;

Jerloma · Post by **Jerloma** » Fri Jul 08, 2016 9:55 am

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.

Ryan · Post by **Ryan** » Fri Jul 08, 2016 9:59 am

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

ETA: Or trying to overwrite into the same cell?

mister d · Post by **mister d** » Fri Jul 08, 2016 10:01 am

If that's not working, you don't have numbers in the original column.

mister d · Post by **mister d** » Fri Jul 08, 2016 10:01 am

Either =trim(A1) or =A1+1-1 should make it numerical.

A_B · Post by **A_B** » Fri Jul 08, 2016 10:01 am

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

Jerloma · Post by **Jerloma** » Fri Jul 08, 2016 10:06 am

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

Ummm...yes. Wow. Carry on.

Jerloma · Post by **Jerloma** » Wed Sep 14, 2016 1:54 pm

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?

A_B · Post by **A_B** » Wed Sep 14, 2016 2:00 pm

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.

Jerloma · Post by **Jerloma** » Wed Sep 14, 2016 2:48 pm

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

A_B · Post by **A_B** » Wed Sep 14, 2016 2:52 pm

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...

mister d · Post by **mister d** » Wed Sep 14, 2016 3:16 pm

You forgot about Fund F. J-Lo has six (6) funds.

A_B · Post by **A_B** » Wed Sep 14, 2016 3:21 pm

mister d wrote:You forgot about Fund F. J-Lo has six (6) funds.

Oh shit. Well I'm out, then.

mister d · Post by **mister d** » Wed Sep 14, 2016 3:31 pm

You failed him in his time sensitive matter.

Jerloma · Post by **Jerloma** » Wed Sep 14, 2016 8:51 pm

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.

A_B · Post by **A_B** » Wed Sep 14, 2016 8:56 pm

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.

A_B · Post by **A_B** » Fri Oct 14, 2016 1:10 pm

Post by **Shirley** » Fri Oct 14, 2016 1:13 pm

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?)

govmentchedda · Post by **govmentchedda** » Mon Oct 17, 2016 10:15 am

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?

A_B · Post by **A_B** » Mon Oct 17, 2016 10:20 am

Not sure how to do it in one column. And why is putting it in two columns bad?

rass · Post by **rass** » Mon Oct 17, 2016 10:28 am

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?

wlu_lax6 · Post by **wlu_lax6** » Mon Oct 17, 2016 10:34 am

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

Ryan · Post by **Ryan** » Mon Oct 17, 2016 10:36 am

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"

A_B · Post by **A_B** » Mon Oct 17, 2016 10:36 am

Countif works, but not twice in the same cell, right?