Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

14 hours ago, Chemical X said:

@Ned  @Ignoramus

formula works, it leaves a couple of #REF! fields....any advise?

Would need to see the spreadsheet.

Share this post


Link to post
Share on other sites

anyone help with a formula for this?

progressive scale for commission.  let's say there is a 200k commission. 

the first 5k is 100%  so 5k

5001-10k is 80%     so 4k

10,001-30k is 70%  so 14k

30,001-100k is 60%  so 42k

100,001+ is 50%  so 50k

 

how to write an excel formula to calculate this for different amounts would win user a prize!

 

@Ned

Share this post


Link to post
Share on other sites
18 minutes ago, Chemical X said:

anyone help with a formula for this?

progressive scale for commission.  let's say there is a 200k commission. 

the first 5k is 100%  so 5k

5001-10k is 80%     so 4k

10,001-30k is 70%  so 14k

30,001-100k is 60%  so 42k

100,001+ is 50%  so 50k

 

how to write an excel formula to calculate this for different amounts would win user a prize!

 

@Ned

 

You mean, "let's say there's a 200k sale", right? So the commission on that is 5k+4k+14k+42K+50K = 155k?

If the sale price is in A1, I think the formula is:

=IF(A1>100000, ((A1-100000)*0.5)+65000, IF(A1>30000, ((A1-30000)*0.6)+23000, IF(A1>10000, ((A1-10000)*0.7)+9000, IF(A1>5000, ((A1-5000)*0.8)+5000, A1)))


Basically, if A1 is in the top range, subtract 100k, take 50% of what's left, then add the previous commissions (65k) back in. Otherwise, repeat down the line.

 

  • Like 2

Share this post


Link to post
Share on other sites
20 minutes ago, Walking Boot said:

 

You mean, "let's say there's a 200k sale", right? So the commission on that is 5k+4k+14k+42K+50K = 155k?

If the sale price is in A1, I think the formula is:

=IF(A1>100000, ((A1-100000)*0.5)+65000, IF(A1>30000, ((A1-30000)*0.6)+23000, IF(A1>10000, ((A1-10000)*0.7)+9000, IF(A1>5000, ((A1-5000)*0.8)+5000, A1)))


Basically, if A1 is in the top range, subtract 100k, take 50% of what's left, then add the previous commissions (65k) back in. Otherwise, repeat down the line.

 

yes and no....

 

logic seems right, but maff is wrong.........I get 115k, not 155k

 

I tried writing the formula, but I get errors since I have no clue how to write this.

Share this post


Link to post
Share on other sites
1 minute ago, Chemical X said:

yes and no....

 

logic seems right, but maff is wrong.........I get 115k, not 155k

 

I tried writing the formula, but I get errors since I have no clue how to write this.

 

Yeah, I typo'd, it should give 115k if "200,000" is in A1.

 

Just copy/paste it into Excel and change all the A1's into whatever cell the number you want to base the commissions off of is in, and it should work.

  • Like 1

Share this post


Link to post
Share on other sites

thx

Edited by Chemical X
sonuva #####, it worked you magnificent bastard!

Share this post


Link to post
Share on other sites

There's probably an easier way to do it, I know I've seen a tax withholding calculator that breaks it down by bracket, just uses more cells. It's a bit wonky but it'll all fit in one cell.

Share this post


Link to post
Share on other sites

What's his prize @Chemical X?

ETA: you left me hanging on your wife's problem. :hot: 

Edited by Ned

Share this post


Link to post
Share on other sites
16 hours ago, Walking Boot said:

There's probably an easier way to do it, I know I've seen a tax withholding calculator that breaks it down by bracket, just uses more cells. It's a bit wonky but it'll all fit in one cell.

There's ways to do it where you can set up a table with the ranges and percentages, so that if these things change over time or are different by rep you can more easily change/customize them, but in a pinch the nested IF formula should work.

Share this post


Link to post
Share on other sites

This started happening yesterday with my Excel 2010.

I try copying data from one excel saved document to another opened excel document and I get a pop up box reading

Microsoft Excel cannot paste the data

 

But I can copy and paste it within the original document.

Text, numbers whatever it won't work.

 

Also trying to change cell formats I'm getting a pop up box reading

Too many different cell formats

 

But if I open a excel sheet and type data into a cell I can change cell formats as many times as I want and don't get the error.

I can also copy and paste data from a previously saved Excel sheet into a newly opened Excel sheet and do not get those errors. 

When I save the sheet and go back to it the error problem returns in that sheet as well.

But I can paste by right clicking and using Paste Option Match Destination Formatting (M), but the Too many different cell formats problem remains.

My default save type for excel sheets is save as Excel Workbook.

 

 

Share this post


Link to post
Share on other sites
2 hours ago, Village Idiot said:

This started happening yesterday with my Excel 2010.

I try copying data from one excel saved document to another opened excel document and I get a pop up box reading

Microsoft Excel cannot paste the data

 

But I can copy and paste it within the original document.

Text, numbers whatever it won't work.

 

Also trying to change cell formats I'm getting a pop up box reading

Too many different cell formats

 

But if I open a excel sheet and type data into a cell I can change cell formats as many times as I want and don't get the error.

I can also copy and paste data from a previously saved Excel sheet into a newly opened Excel sheet and do not get those errors. 

When I save the sheet and go back to it the error problem returns in that sheet as well.

But I can paste by right clicking and using Paste Option Match Destination Formatting (M), but the Too many different cell formats problem remains.

My default save type for excel sheets is save as Excel Workbook.

 

 

Have you completely shut down Excel recently?  I've never seen these errors before, but they both sound to me like you're exhausting resources.  When this happens to me (lack of resources), I have to shut down/restart Excel.

Is it a big range of data?

Share this post


Link to post
Share on other sites
23 minutes ago, Ned said:

Have you completely shut down Excel recently?  I've never seen these errors before, but they both sound to me like you're exhausting resources.  When this happens to me (lack of resources), I have to shut down/restart Excel.

Is it a big range of data?

I closed everything and rebooted my computer yesterday when I saw this issue and it didn't help.

I've tried it with as few as 1 cell of data containing the word No and got the too many formats pop up.

The baffling part is I have never had this issue before no matter how many excel sheets I had open at once. And I've had as many as 6 open at once.

I'll try closing and reopening Excel again.

 

Thanks,.

Share this post


Link to post
Share on other sites

Wow.

I had 2 excel sheets open, closed them both and reopened the one I ad the one cell Too many cell formats error in.

It let me change that cell format after I reopened it. 

I opened the other sheet I had open at the time and it still is letting me have my way with cell formats.

 

It's an inconvenience but if I have to do it to keep doing what I'm doing oh well.

Share this post


Link to post
Share on other sites
Just now, Village Idiot said:

Wow.

I had 2 excel sheets open, closed them both and reopened the one I ad the one cell Too many cell formats error in.

It let me change that cell format after I reopened it. 

I opened the other sheet I had open at the time and it still is letting me have my way with cell formats.

 

It's an inconvenience but if I have to do it to keep doing what I'm doing oh well.

Go to Windows Explorer and check the size.  I'm wondering if the workbook size is huge.  How involved is the sheet?  Would it be easy to copy/paste it to a new workbook and save the new one going forward?

Share this post


Link to post
Share on other sites
9 minutes ago, Ned said:

Go to Windows Explorer and check the size.  I'm wondering if the workbook size is huge.  How involved is the sheet?  Would it be easy to copy/paste it to a new workbook and save the new one going forward?

It's only 160 KB in size and I've tried copying the other sheets I was having this same issue with into a new sheet and it won't let me paste unless I do a paste with destination formatting, and even then sometimes it was cannot paste.

But still pastes it.

Or if I do a paste as a CSV file it works, then I have to completely re format it.

 

And all of these have no links anywhere else or any massive formulas in them that take a long time to update if changes are made.

 

I have far larger excel sheets I've created that have a lot of moving parts and macros that still work fine when I use them by pasting in data from word or a SAS generated e-mail containing data, and firing off a macro I've created to do an update.

 

 

 

 

Edited by Village Idiot

Share this post


Link to post
Share on other sites
33 minutes ago, Village Idiot said:

It's only 160 KB in size and I've tried copying the other sheets I was having this same issue with into a new sheet and it won't let me paste unless I do a paste with destination formatting, and even then sometimes it was cannot paste.

But still pastes it.

Or if I do a paste as a CSV file it works, then I have to completely re format it.

 

And all of these have no links anywhere else or any massive formulas in them that take a long time to update if changes are made.

 

I have far larger excel sheets I've created that have a lot of moving parts and macros that still work fine when I use them by pasting in data from word or a SAS generated e-mail containing data, and firing off a macro I've created to do an update.

Have you looked at this KB article?

  • Like 1

Share this post


Link to post
Share on other sites
31 minutes ago, bcdjr1 said:

Have you looked at this KB article?

I have not seen that, that helps.

Thanks!

Share this post


Link to post
Share on other sites

I am setting up the spreadsheet for a Music Draft, and I have it set up 95%. I am having issues with creating sorted spreadsheets (e.g. Category, Drafter, Artist, etc.) based on the main sheet.

1) I am using this formula to grab the data from the main sheet:


=IF(ISBLANK(DRAFT!E2),"",+DRAFT!E2) - there isn't a space between the quotes.

but when I sort the blank cells come first. From what I have read briefly, the blank fields were supposed to come last. (although this might be when performed on a list of numbers)

2) When I try to sort based on the Categories (in the main sheet I have set up a Validation/Pull Down list from a separate sheet for the Categories to make Category selection easier) the blank cells come in the middle of the list. (I have also setup a custom sort list based upon the Category list).

Any thoughts?

TIA!

 

Share this post


Link to post
Share on other sites
1 hour ago, Matthias said:

Are you sorting the formulas? Or turning your entries into hard coded names?

Here's what I'd do. 

#1: use vlookup. then transform all of the variable row references to fixed. 

#2: do a simple if function that if the cell is empty, 999. Else, 1.

#3: sort by your 999 column first and then your band or whatever second. 

As far as the sorting, I am using the 'Sort' function from the menu. I think I'm sorting the cells. ???

In number 1, do I transform the references using $s in the formula?

OK, I understand the #2 and #3. That's clever!

Now the next silly question is all of this will work on Google Sheets? I need to put this online for everyone to look at/add their picks.

I don't think I can do a custom sort on Google Sheets.

Thanks for the quick response!!

 

 

Share this post


Link to post
Share on other sites
8 hours ago, Matthias said:

Here you go. I figured it out.

https://docs.google.com/spreadsheets/d/1UkQsrasHvk6bo6b8qypn-3350vHk5OC1bBx-c4r40Sg/edit?usp=sharing

In column D, you have if something is there, it's the # above +1. If it's empty, it's just the # above. Then do a vlookup for the #s going down the list and anything that's missing just will be skipped.

Thanks! I will have to dig into this a bit. I got a couple Excel books at the library yesterday. I'll check them for vLookup.

I will put in v0.1 online for the draft and work on the sorted sheets.

Thanks again for your help.

Share this post


Link to post
Share on other sites

Hello,

I have 2 date fields:

Column I8: 10/10

Column I9: 10/17

In Column I10, I want to subtract the difference in dates (I9 - I8), including only Business Days, and return a value of 5.

How do I go about doing that?

Edited by cubd8

Share this post


Link to post
Share on other sites
Guest

Networkdays.intl

When it seems like something should be possible, hit F1.

Share this post


Link to post
Share on other sites

networkdays.intl if you want to specify your weekends, and networkdays without the .intl if you want it to assume Saturdays and Sundays are off

Share this post


Link to post
Share on other sites

Hello,

I have a question with a look-up file that I am hoping for help on.

Look-up Data Tab Name: Errors
Data: Column B, Row 2+

Example of Data:
12345
11111
22222

in my main workbook, in Column CG, I want to output 2 possible values if data in Column B matches the data in "Errors" look-up tab.
If the data in Column B matches the data in "Errors", output a '1'.
If the data in Column B does not match the data in "Errors", output a '0'.

Share this post


Link to post
Share on other sites

Need to know what column the data you're looking up against on the error tab to complete this(bold)...I wrote it on my iPhone, so I can't guarantee it's error free.  You should get the drift though...

=IF(ISERROR(MATCH(B1,Errors!D:D,0)),0,1)

Share this post


Link to post
Share on other sites

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

Share this post


Link to post
Share on other sites
45 minutes ago, tjnc09 said:

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

Try paste without formatting or paste as plain text.

Edited by hagmania
  • Like 1

Share this post


Link to post
Share on other sites
1 hour ago, tjnc09 said:

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

I recommend ASAP Utilities, which has a function called "Remove all objects" that will get rid of those.

http://www.asap-utilities.com/

 

  • Like 3

Share this post


Link to post
Share on other sites
1 hour ago, hagmania said:
2 hours ago, tjnc09 said:

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

Try paste without formatting or paste as plain text.

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

Edited by GregR
  • Like 4

Share this post


Link to post
Share on other sites

Thanks all.  It's a site I copy from daily.  I copied the text/paste value to another sheet, relinked my formulas and deleted my old sheet w all the pictures.  That works but not something I want to do daily.  Cut my size from 5.5MB to 2.5 MB (eight days of pastes) so it's definitely a problem.

 

Will check out ASAP.   Appreciate it :thumbup:

Share this post


Link to post
Share on other sites
11 minutes ago, GregR said:

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

Yeah, I have some fairly complex formulas to format the data I pull from the site already so I was trying not to have to redo them.  I'm sure if I paste as text or from another source I would have to redo the formulas.

Share this post


Link to post
Share on other sites
22 minutes ago, tjnc09 said:

Thanks all.  It's a site I copy from daily.  I copied the text/paste value to another sheet, relinked my formulas and deleted my old sheet w all the pictures.  That works but not something I want to do daily.  Cut my size from 5.5MB to 2.5 MB (eight days of pastes) so it's definitely a problem.

 

Will check out ASAP.   Appreciate it :thumbup:

Depending on the formatting of the website you're pulling from, you can make it auto download from the site. You can use websites as an external data source - see the Data ribbon.

 

It's pretty easy to set up. Once you set that up, the data connection will save with your workbook. Then all you have to do is hit refresh any time you want to update the data. 

Share this post


Link to post
Share on other sites
12 minutes ago, Ned said:

Depending on the formatting of the website you're pulling from, you can make it auto download from the site. You can use websites as an external data source - see the Data ribbon.

 

It's pretty easy to set up. Once you set that up, the data connection will save with your workbook. Then all you have to do is hit refresh any time you want to update the data. 

Yeah, I had previously tried that using Data->Get External Data->From Web.  The data I want to copy in never actual shows up when I go to link it, unfortunately.

Share this post


Link to post
Share on other sites

Here's another question.

In a new tab, I want to aggregate the data from a tab called 'Data', using Column A (Associate) to sum up the scores that an associate has in Column B (Score) to create 2 new values:
Data Tab:
Column A: Associate
Column B: Score

Create these new values in a new tab:
# of Orders:
Total Score:

However, as an added twist, I want to exclude (drop) any scores LT 40 below. 

So:
Brian 4 orders and an average of 97.5 (390/4)
Julie 3 orders and an average of 116.6

Associate: Score:
Brian 40
Julie 30
Steve 30
Bob 20
Joe 10

Brian 100
Julie 100
Steve 100
Bob 100
Joe 100
Brian 200
Julie 200
Steve 200
Bob 200
Joe 200
Brian 10
Julie 10
Steve 10
Bob 10
Joe 10

Brian 50
Julie 50
Steve 50
Bob 50
Joe 50

Share this post


Link to post
Share on other sites
1 hour ago, cubd8 said:

Here's another question.

In a new tab, I want to aggregate the data from a tab called 'Data', using Column A (Associate) to sum up the scores that an associate has in Column B (Score) to create 2 new values:
Data Tab:
Column A: Associate
Column B: Score

Create these new values in a new tab:
# of Orders:
Total Score:

However, as an added twist, I want to exclude (drop) any scores LT 40 below. 

So:
Brian 4 orders and an average of 97.5 (390/4)
Julie 3 orders and an average of 116.6

Associate: Score:
Brian 40
Julie 30
Steve 30
Bob 20
Joe 10

Brian 100
Julie 100
Steve 100
Bob 100
Joe 100
Brian 200
Julie 200
Steve 200
Bob 200
Joe 200
Brian 10
Julie 10
Steve 10
Bob 10
Joe 10

Brian 50
Julie 50
Steve 50
Bob 50
Joe 50

Use a =SUMIF > 39

Share this post


Link to post
Share on other sites

got a hard to describe question.

 

wife is building an excel with multiple tabs.  on page 1 she is summarizing info like so:

 

open stores - 15

closed stores - 10

relocate stores - 5

 

on one of the other tabs all the info she is using on the summary tab is contained.  she wants to make the make words "open stores" become clickable and take her to that info tab showing only open stores.  then make "closed stores" clickable to do the same.

 

so far, all I can figure out is how to make them clickable, but not not remove the unwanted items.

 

any help would be great!

Share this post


Link to post
Share on other sites
3 minutes ago, Chemical X said:

got a hard to describe question.

 

wife is building an excel with multiple tabs.  on page 1 she is summarizing info like so:

 

open stores - 15

closed stores - 10

relocate stores - 5

 

on one of the other tabs all the info she is using on the summary tab is contained.  she wants to make the make words "open stores" become clickable and take her to that info tab showing only open stores.  then make "closed stores" clickable to do the same.

 

so far, all I can figure out is how to make them clickable, but not not remove the unwanted items.

 

any help would be great!

So you want each cell be a hyperlink to different tabs named "open stores", "closed stores", etc?

Share this post


Link to post
Share on other sites
1 minute ago, Ned said:

So you want each cell be a hyperlink to different tabs named "open stores", "closed stores", etc?

well, lets say there is a 99 column sheet that says, open, closed, relocate throughout. 

on that main page, she wants to click the word closed and have it bring up the closed stores only from the 99 columns, leaving out the other 66.

Share this post


Link to post
Share on other sites
2 minutes ago, Chemical X said:

ooops, rows.

so, the filtering is great, but I want to click the name on tab 1 and have it bring up that info from tab 2, filtering out the unwanted info.

Share this post


Link to post
Share on other sites
12 minutes ago, Chemical X said:

so, the filtering is great, but I want to click the name on tab 1 and have it bring up that info from tab 2, filtering out the unwanted info.

That would require a macro. I could write something but would need the workbook. 

Share this post


Link to post
Share on other sites

need a formula.....

have a column of dates, let's say column A.

I would like a formula that can separate these dates into specific fiscal years.  i.e. - company's fiscal year is 10/1-9/30.

 

so a formula that breaks out column A dates for FYE 2016, 2017, 2018, 2019, 2020, etc.

 

any ideas?

Share this post


Link to post
Share on other sites
4 minutes ago, Chemical X said:

need a formula.....

have a column of dates, let's say column A.

I would like a formula that can separate these dates into specific fiscal years.  i.e. - company's fiscal year is 10/1-9/30.

 

so a formula that breaks out column A dates for FYE 2016, 2017, 2018, 2019, 2020, etc.

 

any ideas?

=IF(MONTH(A1)<10,YEAR(A1),YEAR(A1)+1)

 

?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.