Excel

wlu_lax6 · Post by **wlu_lax6** » Fri May 03, 2013 11:55 am

Since I know we have some Excel users here...this is a good one to back pocket.
http://www.businessinsider.com/excel-tr ... ray-2013-5" onclick="window.open(this.href);return false;

Post by **Shirley** » Mon Sep 16, 2013 3:39 pm

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.

Ryan · Post by **Ryan** » Wed Apr 16, 2014 12:28 pm

Is anyone out there extremely proficient with INDEX, MATCH, and INDIRECT commands? I'm in over my head.

wlu_lax6 · Post by **wlu_lax6** » Wed Apr 16, 2014 12:37 pm

Ryan wrote:Is anyone out there extremely proficient with INDEX, MATCH, and INDIRECT commands? I'm in over my head.

http://www.businessinsider.com/excel-index-match-2013-7" onclick="window.open(this.href);return false;

Have not used indirect but seems very useful for using the value of a cell inside of a formula
http://www.excel-easy.com/examples/indirect.html" onclick="window.open(this.href);return false;

kranepool · Post by **kranepool** » Tue May 06, 2014 9:20 pm

In a large table, I have a list with thousands of clients, 200 different products and revenue associated for each line.

If the Product is A, B, or E, I need to divide the revenue by 3. Otherwise, I need to leave the value as-is.

I've tried ISNUMBER, MATCH, IF, and I'm just blanking after a very long day. I know there's a straightforward solution to this. I'm crispy.

Code: Select all

Prod	Rev
A	100
B	300
C	250
D	6
E	125
F	33
G	50

wlu_lax6 · Post by **wlu_lax6** » Wed May 07, 2014 7:21 am

there is a really complex way to do divideif (similar to sumif function), but if is this really this simple nested if statements should do the trick. Basically the Structure is If, Then, Else 3 times where the Else becomes the 2nd test, and the Else of the 2nd test becomes the 3rd test).

=if (a1 ='A', b2/3, if (a1='B', b2/3, if (a1='C', b2/3, b2)))

mister d · Post by **mister d** » Wed May 07, 2014 8:05 am

Yup, either that or a 3rd column with 3 next to A, B, C and 1 next to the rest then a lookup to divide by the new column.

A_B · Post by **A_B** » Wed May 07, 2014 8:12 am

I don't think Wlu's formula would work if you're copying it to a bunnch of different cells, presumable with a classification of A-G...
maybe I am misunderstanding your goal, but it appears to me that you have to be associating a product with a code A-G...so every product code has to be represented in the formula, not just the ones that are to be divided by three.

here is what I came up with for the cells starting in C16 and copied down, and in this, where B16 is the cell in my data that has the identifier:

=IF(B16="A",$B$2/3,IF(B16="B",$B$3/3,IF(B16="E",$B$6/3,IF(B16="C",$B$4,IF(B16="D",$B$5,IF(B16="F",$B$7,$B$8))))))

Here's the chart to show the references...

A_B · Post by **A_B** » Wed May 07, 2014 8:15 am

Edit..I missed the 200 different products part. That would be a fucked up IF that I don't think it would be able to nest that many. So Yeah, I'd go with Mr. D's and do the calculation in a new column and then use vlookup to reference that cell.

wlu_lax6 · Post by **wlu_lax6** » Wed May 07, 2014 8:17 am

I was assuming a simple single table where everything was in the one row. My Excel preference is to always "show my work" and use extra cells to have interim steps (which I then hide). This way you keep formulas smaller and when there is a problem you can go step by step.

wlu_lax6 · Post by **wlu_lax6** » Wed May 07, 2014 8:20 am

AB_skin_test wrote:Edit..I missed the 200 different products part. That would be a fucked up IF that I don't think it would be able to nest that many. So Yeah, I'd go with Mr. D's and do the calculation in a new column and then use vlookup to reference that cell.

The nesting only becomes bad based on the number of products that need to have a number other than what was provided. If there are 200 products but only 3 need the divide by 3, the formula is like the one I used. If you have 50 of them that need a modification, then it gets ugly.

wlu_lax6 · Post by **wlu_lax6** » Wed May 07, 2014 8:21 am

Side note: Even when I worked at MSFT I could not figure out why there was not a simple "case" function or "in" function concept in Excel.

A_B · Post by **A_B** » Wed May 07, 2014 8:24 am

wlu_lax6 wrote:
AB_skin_test wrote:Edit..I missed the 200 different products part. That would be a fucked up IF that I don't think it would be able to nest that many. So Yeah, I'd go with Mr. D's and do the calculation in a new column and then use vlookup to reference that cell.
The nesting only becomes bad based on the number of products that need to have a number other than what was provided. If there are 200 products but only 3 need the divide by 3, the formula is like the one I used. If you have 50 of them that need a modification, then it gets ugly.

Right, my first crack was going to be too hard to do if you had to nest more than a few. Excel used to limit it to 14 or so...not sure if they upped that limit, but either way it woul dbee too hard of a formulat to troubleshoot.

And I assume the problem was simplified just for ease of description, which didn't work for me because I am an idiot.

Post by **Sabo** » Wed May 07, 2014 8:26 am

This thread makes my brain hurt.

mister d · Post by **mister d** » Wed May 07, 2014 8:30 am

Kranepool must be pretty dumb if we came up with two effective solutions in minutes.

Post by **Shirley** » Wed May 07, 2014 9:18 am

mister d wrote:Yup, either that or a 3rd column with 3 next to A, B, C and 1 next to the rest then a lookup to divide by the new column.

I think this might actually be the best solution. It makes for a simple formula, it doesn't hide the critical divisors, and it allows for easy future modifications (and different divisors). If you want, you could even hide the divisor column.

kranepool · Post by **kranepool** » Wed May 07, 2014 9:25 am

I thank you all immensely.

mister d wrote:Kranepool must be pretty dumb if we came up with two effective solutions in minutes.

You have no idea.

One of my direct reports - who's worked for me for years through two different companies - sent me a summary of some Chik-Fil-A leadercast thingy recently and we had the following exchange:

Quote from article: Andy Stanley, pastor of NorthPoint Church and bestselling author and communicator: Level five leaders don’t feel the need to be the smartest person in the room. They are willing to be the dumbest person.

Me to direct report: "This accurately captures my entire leadership experience."

Direct report: "You're a level six."

Gunpowder · Post by **Gunpowder** » Wed May 07, 2014 10:05 am

That's hilarious.

mister d · Post by **mister d** » Wed May 07, 2014 11:09 am

And the avatar bumps it up another level.

That French Guy · Post by **That French Guy** » Thu May 08, 2014 8:54 am

AB_skin_test wrote:
wlu_lax6 wrote:
AB_skin_test wrote:Edit..I missed the 200 different products part. That would be a fucked up IF that I don't think it would be able to nest that many. So Yeah, I'd go with Mr. D's and do the calculation in a new column and then use vlookup to reference that cell.
The nesting only becomes bad based on the number of products that need to have a number other than what was provided. If there are 200 products but only 3 need the divide by 3, the formula is like the one I used. If you have 50 of them that need a modification, then it gets ugly.

Right, my first crack was going to be too hard to do if you had to nest more than a few. Excel used to limit it to 14 or so...not sure if they upped that limit, but either way it woul dbee too hard of a formulat to troubleshoot.

And I assume the problem was simplified just for ease of description, which didn't work for me because I am an idiot.

If you have to use lots of IF (and you're not going with MrD's third column solution), you can also write a small macro. The "If Cells(3, 2).Value = 2 Or Cells(3, 2).Value = 3 Then XXX" structure makes it easier to read (one IF/THEN by line) than a 10-size nesting IF formula and you can put in other commands (for example, cell formatting to make it easier to spot which product has which division factor).
Of course, the main (but still small) drawback is you have to launch the macro every time.

A_B · Post by **A_B** » Thu May 08, 2014 8:58 am

I think I speak for everyone when I say fuck macros.

degenerasian · Post by **degenerasian** » Wed Nov 19, 2014 12:15 pm

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

mister d · Post by **mister d** » Wed Nov 19, 2014 12:24 pm

I think you'd have to use Text to Columns by space then sum the first column.

wlu_lax6 · Post by **wlu_lax6** » Wed Nov 19, 2014 2:32 pm

Mr. D's is the fastest way. You could also write a formula to parse out the number using some of the text manipulation formulas and the forumla to change use text as a number value...but by the time you did that you could have just text to columns and hit the sum button.

elflaco · Post by **elflaco** » Wed Nov 19, 2014 4:00 pm

text to columns.
5seconds to copy, separate, click finish.

mister d · Post by **mister d** » Wed Nov 19, 2014 4:09 pm

elflaco's is the fastest way. You could also write a formula to parse out the number using some of the text manipulation formulas and the forumla to change use text as a number value...but by the time you did that you could have just text to columns and hit the sum button.

A_B · Post by **A_B** » Wed Nov 19, 2014 4:10 pm

At this point he could have retyped all of the entries into new columns

mister d · Post by **mister d** » Wed Nov 19, 2014 4:13 pm

(Also, the check number is wrong. Probably a typo.)

degenerasian · Post by **degenerasian** » Wed Nov 19, 2014 4:31 pm

AB_skin_test wrote:At this point he could have retyped all of the entries into new columns

i have many lists. Some over 20000 entries.

A_B · Post by **A_B** » Wed Nov 19, 2014 4:32 pm

degenerasian wrote:
AB_skin_test wrote:At this point he could have retyped all of the entries into new columns
i have many lists. Some over 20000 entries.

I stand by my statements.

degenerasian · Post by **degenerasian** » Wed Nov 19, 2014 4:48 pm

Text To Column worked. Thanks!

govmentchedda · Post by **govmentchedda** » Thu Apr 09, 2015 2:42 pm

Is there a way in Excel to take the three lowest number and total them?

I've got a Masters pool, and the players each picked four golfers and our lowest three scores count. I'd love to be able to have this done automatically.

wlu_lax6 · Post by **wlu_lax6** » Thu Apr 09, 2015 2:46 pm

govmentchedda wrote:Is there a way in Excel to take the three lowest number and total them?

I've got a Masters pool, and the players each picked four golfers and our lowest three scores count. I'd love to be able to have this done automatically.

something like this may work. The key is the "Small" function in Excel and using the range.
=SUM(SMALL(IF(C2:F2>0,C2:F2),{1,2,3}))

govmentchedda · Post by **govmentchedda** » Thu Apr 09, 2015 2:58 pm

Thanks, I'll fiddle around with this tonight.

A_B · Post by **A_B** » Thu Apr 09, 2015 2:58 pm

It's a pretty simple re-sort/sum too. Probably as easy as troubleshooting the formulae.

ACtually, if you have just four people each, the easiest way might be to so a sum and then subtract the max

=(SUM(E5:E8))-(MAX(E5:E8))

govmentchedda · Post by **govmentchedda** » Thu Apr 09, 2015 3:00 pm

Essentially, I've got the golfers listed in one column, and the scores in another. I'd like to do a SUM (or other helpful) function that takes the three lowest scores and totals them. Shit, if I could have it rerank us gamblers based on our golfer's scores, that would be sweet too. Like a leaderboard.

A_B · Post by **A_B** » Thu Apr 09, 2015 3:02 pm

govmentchedda wrote:Essentially, I've got the golfers listed in one column, and the scores in another. I'd like to do a SUM (or other helpful) function that takes the three lowest scores and totals them. Shit, if I could have it rerank us gamblers based on our golfer's scores, that would be sweet too. Like a leaderboard.

See my last edit. Might work. I did a test and it does work

mister d · Post by **mister d** » Thu Apr 09, 2015 3:19 pm

This is mine in google docs which I think works the same ... =sum(I4:L4)-MAX(I4:L4)

A_B · Post by **A_B** » Thu Apr 09, 2015 3:27 pm

I think to get it to make you a automatic "leaderboard" you'd have to do a VBA macro type thingy. But you could do a Pseudo leaderboard just by putting every of your pool member's name in a List with a link to their unique score and then re-sort that. Would be one resort instead of manually redoing it. You'd want to lock the cell references in the "leaderboard" to make sure it didn't foul up. That isn't hard of course.

govmentchedda · Post by **govmentchedda** » Fri Apr 10, 2015 2:32 pm

My spreadsheet is glorious. Thanks, gents.