The Swamp

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.

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.

I'm fine with a cell for wins, and a cell for losses.

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.

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

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.

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.

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?

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

What the fuck man.

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.

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

Or are you all welcome?

Thanks, dicks. I could've used the Excel help.

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.

This is from 2012 but the days match:

https://drive.google.com/previewtemplat ... ode=public

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.

Oh my god, this was great

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?

Select where you want the freeze to take place, then go to View --> Freeze Panes.

go to the far left and upmost cell below your headers, though.

thx dudes

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.

Excel + mouse = clown

Sweet, imma cool kid.

Alt Tab Crew.

Ctrl Alt V, V, bitch.

Step up your games
http://www.businessinsider.com/excel-ke ... mac-2017-5

and for you PowerPoint weasel types
http://www.businessinsider.com/microsof ... uts-2017-6

Square Rob wrote:Ctrl Alt V, V, bitch.

Must be a custom keyboard for you if it has a bitch key!!!!!!!

I'm almost exclusively keyboard in Excel, too, though my new version certainly is trying to push the ribbons.

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

Either custom number format of 000 or add an apostrophe in front of the 000.

The latter was tried first and worked and thanks.

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.

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.

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

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?

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.

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.

Would building a summary section, using COUNTIF and running the graph off there count as manual?

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.