Excel

Okay . . . let's try this again.

Moderators: Shirley, Sabo, brian, rass, DaveInSeattle

User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Excel

Post by wlu_lax6 »

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;
User avatar
Shirley
The Dude
Posts: 7516
Joined: Mon Mar 11, 2013 2:32 pm

Re: Excel

Post by Shirley »

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.
Totally Kafkaesque
User avatar
Ryan
The Dude
Posts: 10439
Joined: Mon Mar 18, 2013 10:01 am

Re: Excel

Post by Ryan »

Is anyone out there extremely proficient with INDEX, MATCH, and INDIRECT commands? I'm in over my head.
he’s a fixbking cyborg or some shit. The

holy fuckbAllZ, what a ducking nightmare. Holy shot. Just, fuck. The
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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;
User avatar
kranepool
Bunny Lebowski
Posts: 575
Joined: Tue Mar 19, 2013 8:46 am

Re: Excel

Post by kranepool »

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
mini puke to 1,558
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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)))
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

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

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

Image
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 »

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.
You know what you need? A lyrical sucker punch to the face.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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.
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:
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.
You know what you need? A lyrical sucker punch to the face.
User avatar
Sabo
The Dude
Posts: 5466
Joined: Mon Mar 11, 2013 8:33 am
Location: On the trail

Re: Excel

Post by Sabo »

This thread makes my brain hurt.
Birds don’t suck. They lack the necessary anatomical structures to do so.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Kranepool must be pretty dumb if we came up with two effective solutions in minutes.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Shirley
The Dude
Posts: 7516
Joined: Mon Mar 11, 2013 2:32 pm

Re: Excel

Post by Shirley »

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.
Totally Kafkaesque
User avatar
kranepool
Bunny Lebowski
Posts: 575
Joined: Tue Mar 19, 2013 8:46 am

Re: Excel

Post by kranepool »

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."
mini puke to 1,558
Gunpowder
The Dude
Posts: 8525
Joined: Mon Mar 11, 2013 7:52 am
Location: Dipshitville, FL
Contact:

Re: Excel

Post by Gunpowder »

That's hilarious.
Pack a vest for your james in the city of intercourse
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

And the avatar bumps it up another level.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
That French Guy
Maude Lebowski
Posts: 187
Joined: Thu Mar 14, 2013 2:36 pm

Re: Excel

Post by That French Guy »

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.
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 think I speak for everyone when I say fuck macros.
You know what you need? A lyrical sucker punch to the face.
User avatar
degenerasian
The Dude
Posts: 12302
Joined: Tue Mar 12, 2013 12:22 pm

Re: Excel

Post by degenerasian »

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
Kung Fu movies are like porn. There's 1 on 1, then 2 on 1, then a group scene..
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

I think you'd have to use Text to Columns by space then sum the first column.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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
Brandt
Posts: 485
Joined: Sat Mar 16, 2013 4:46 am
Location: Exit 10

Re: Excel

Post by elflaco »

text to columns.
5seconds to copy, separate, click finish.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

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

At this point he could have retyped all of the entries into new columns
You know what you need? A lyrical sucker punch to the face.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

(Also, the check number is wrong. Probably a typo.)
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
degenerasian
The Dude
Posts: 12302
Joined: Tue Mar 12, 2013 12:22 pm

Re: Excel

Post by degenerasian »

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.
Kung Fu movies are like porn. There's 1 on 1, then 2 on 1, then a group scene..
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 »

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.
You know what you need? A lyrical sucker punch to the face.
User avatar
degenerasian
The Dude
Posts: 12302
Joined: Tue Mar 12, 2013 12:22 pm

Re: Excel

Post by degenerasian »

Text To Column worked. Thanks!
Kung Fu movies are like porn. There's 1 on 1, then 2 on 1, then a group scene..
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

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.
Until everything is less insane, I'm mixing weed with wine.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

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}))
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

Thanks, I'll fiddle around with this tonight.
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 »

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))
Last edited by A_B on Thu Apr 09, 2015 3:02 pm, edited 1 time in total.
You know what you need? A lyrical sucker punch to the face.
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

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

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
You know what you need? A lyrical sucker punch to the face.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

This is mine in google docs which I think works the same ... =sum(I4:L4)-MAX(I4:L4)
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 »

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.
You know what you need? A lyrical sucker punch to the face.
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel

Post by govmentchedda »

My spreadsheet is glorious. Thanks, gents.
Until everything is less insane, I'm mixing weed with wine.
Post Reply