The Swamp

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;

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.

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

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;

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.

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

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

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.

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.

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.

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.

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.

This thread makes my brain hurt.

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

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.

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

That's hilarious.

And the avatar bumps it up another level.

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.

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

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

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

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.

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

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.

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

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

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.

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.

Text To Column worked. Thanks!

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.

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

Thanks, I'll fiddle around with this tonight.

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

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.

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

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

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.

My spreadsheet is glorious. Thanks, gents.