Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Ned

Recommended Posts

21 minutes ago, Ned said:

I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.microsoft.com/en-us/kb/294683

This would be my suggestion as well. I primarily have used mail merge for creating mailing labels, but I don't see why you couldn't create a 4x6 layout with the fields you need and essentially get the same thing you are looking for.

The non-Excel way would be to use Access to do this, but that would be a bit more complicated than I think you need.

Edited by acarey50
Link to comment
Share on other sites

15 minutes ago, Walking Boot said:

Just checking, there's no way to return a Null value with an IF formula, right? I can't use " " because it gives me a #VALUE error in a formula elsewhere, and if I use 0, it looks awful because the format for the cell is a date, so it shows 1/1/1904 instead of 0 or nothing. Ideally, I want it to return as if it's an empty cell.

That's basically a circular issue if you think about it - you're asking it to delete itself.

In your other formula that's referencing this cell, why not add an IF statement to check if it's ""?  Otherwise, you'd need to write a quick macro to wipe out your "" or 0.

=IF(I10="","yup","nope")

Link to comment
Share on other sites

1 hour ago, Walking Boot said:

Just checking, there's no way to return a Null value with an IF formula, right? I can't use " " because it gives me a #VALUE error in a formula elsewhere, and if I use 0, it looks awful because the format for the cell is a date, so it shows 1/1/1904 instead of 0 or nothing. Ideally, I want it to return as if it's an empty cell.

 

Instead of " " just put in ""    So the formula reads =IF(expression you are checking, "return this if true, "")     If the expression works out to be not true, you get a blank cell returned.

If that does not work, or if I am misunderstanding the question, perhaps you could use the ISERROR() function of the ISBLANK(). I use this a lot, especially with VLOOKUP or MATCH.

Basically the formula structures as follows  

 

=IF(ISERROR(Your statement to check),"Value to return if it is an error", "Value to return if it is not"))

 

if checking for blank cells, then use ISBLANK in lieu of ISERROR

Link to comment
Share on other sites

1 hour ago, Walking Boot said:

 

Yeah, that could do it, except other people in the office use this spreadsheet and they won't understand it. :kicksrock:

Trying to "automate" as much as possible for the simpletons I work with.

Also tried using INDEX(MATCH()) and that also returns a '0' if the cell it's looking for is blank, which is disappointing. I was hoping to find some kind of formula where I could get an empty value that wouldn't give me #VALUE all over the place.

I think I'm just going to go back to doing it the manual way, which introduces more possibility for error, but, is also paradoxically more idiot-proof.

Not exactly sure what you doing, but I think you could use an IFERROR() in those cells that you are getting a #VALUE in.  If the formula produces an error you can have it gracefully display nothign with IFERROR(FORMULA,"")

https://exceljet.net/excel-functions/excel-iferror-function

Link to comment
Share on other sites

1 minute ago, Walking Boot said:

 

Sadly, the cell I want to gracefully display nothing isn't the one I'm getting the #VALUE error in. It's in a range of cells elsewhere, which the cell that contains #VALUE is looking at.

 

Thanks everyone for trying, but I think this is one of those things that excel just can't do neatly.

If you wrap an IFERROR() around any #VALUE error cell it will not display a "#VALUE" if that's what you are trying to do.

Link to comment
Share on other sites

6 hours ago, Ned said:

I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.microsoft.com/en-us/kb/294683

Alright, I am on the right track. My one issue now is when I merge my excel report into a label on Word, the date gets jumbled. On Excel it appears as 7/11/16 and then when it merges into Word it turns into stuff that makes no sense, like this...42562. I googled a bunch of stuff and found the issues but have not had success in getting it solved. I am on Office 365. Any ideas what I need to do?

Edited by Beef Ravioli
Link to comment
Share on other sites

3 minutes ago, Beef Ravioli said:

Alright, I am on the right track. My one issue now is when I merge my excel report into a label on Word, the date gets jumbled. On Excel it appears as 7/11/16 and then when it merges into Word it turns into stuff that makes no sense, like this...42562. I googled a bunch of stuff and found the issues but have not had success in getting it done. I am on Office 365. Any ideas what I need to do?

Need to add a date format to the mergefield in Word.. a la {MERGEFIELD MyDate \@ "dd/MMM/yyyy"}

Link to comment
Share on other sites

47 minutes ago, Walking Boot said:

Thanks, but that won't do the trick either. Doesn't matter if I use " " or "", I still get a #VALUE error in a different formula that references this range of cells. Even though the cell itself looks blank, it's actually a data type that throws an error in the second formula (it's WORKDAY, btw, and I'm trying to get a range of cells that list the holidays it should skip based on what country is entered in a cell elsewhere. Like, type "US" in A1, and it'll show a list of US holiday dates in the range on another part of the worksheet. Type "CANADA" and it'll list the Canadian holidays. Etc). The thing I don't like is that if I use "" or " ", WORKDAY can't work with that. I could use 0, or, just nothing, like: =IF(TRUE, 1, ) which WORKDAY is OK with, but that displays '1/1/1904' in the cell and that looks really really ugly and will confuse a lot of my coworkers.

The whole thing works except only with 1/1/1904 all over the place. :kicksrock:

If you build the If statement to return something such as "Not a Holiday" instead of the null value, will your WORKDAY formula work correctly?

Another work around would be to conditionally format the row that your IF statement is in so that if it returns 1/1/1904 (which I believe is the 0 value for date fields), the font is the same color as the cell fill so it appears blank, thus not displaying a value that confuses your coworkers.

Did some googling and it appears there is no way for Excel to return a truly empty cell as the result of a formula, as it does have the formula in there.

Link to comment
Share on other sites

18 minutes ago, Beef Ravioli said:

Alright, I am on the right track. My one issue now is when I merge my excel report into a label on Word, the date gets jumbled. On Excel it appears as 7/11/16 and then when it merges into Word it turns into stuff that makes no sense, like this...42562. I googled a bunch of stuff and found the issues but have not had success in getting it done. I am on Office 365. Any ideas what I need to do?

Make sure that your date fields are formatted as dates in Excel. If they aren't, Word can have some trouble picking up that they are dates and will display them as numbers. See if you are lucky and the solution is as simple as that.

Link to comment
Share on other sites

13 minutes ago, acarey50 said:

Make sure that your date fields are formatted as dates in Excel. If they aren't, Word can have some trouble picking up that they are dates and will display them as numbers. See if you are lucky and the solution is as simple as that.

Is that a fairly simple process?

Link to comment
Share on other sites

54 minutes ago, Walking Boot said:

 

To the first question, no. Having it return words screws up the WORKDAY() function.

 

I figured out a workaround, though. I can get it to display "0" in a cell, instead of 1/1/1904, which is good enough. I forced it using TEXT(). Specifically,

=IF(TRUE,C1,TEXT(0,"h"))

So now, if the evaluation is true, it displays the date saved in C1 in the cell. If the evaluation is false, it displays the hour of 1/1/1904, which is 0. Quite an odd kludge, but it'll work for now.

My coworkers will never understand that part of the formula, but at least it displays ok. Now I have a column full of either dates or 0s, so it's easy to tell at a glance which are the holidays (the actual dates) and which aren't (the zeroes).

You can format the date cells as a date and display a zero value as blank at the same time by creating a custom number format.  Under format cells, select the Date format that you want to use (say "m/d/yyyy") and then choose Custom and edit the format by adding two semi-colons at the end ("m/d/yyyy;;")  What this will do is display a date for positive values and display a blank cell for negative and zero values.

  • Like 2
Link to comment
Share on other sites

10 hours ago, Walking Boot said:
10 hours ago, SantaRosaUte said:

You can format the date cells as a date and display a zero value as blank at the same time by creating a custom number format.  Under format cells, select the Date format that you want to use (say "m/d/yyyy") and then choose Custom and edit the format by adding two semi-colons at the end ("m/d/yyyy;;")  What this will do is display a date for positive values and display a blank cell for negative and zero values.

This works even better, thanks! The 0s aren't displayed as 1/1/1904, and hiding them doesn't mess up the WORKDAY function. Neat trick!

 

:goodposting: 

Props for this, @SantaRosaUte.  I didn't know this one!

Link to comment
Share on other sites

  • 2 weeks later...

Google docs question:

2 Worksheets:  Draft Board & Player Selection

=filter('Player Selection'!C1:D303,ISERROR('Player Selection'!A1:A303))

Column A on Draft Board has this filter.  It is going to look at the Player Selection sheet and only show those that have an error (not selected).

=VLOOKUP(C1,'Draft Board'!D3:V24,2,0)

I have also tried

=VLOOKUP(C1,'Draft Board'!D3:D24,'Draft Board'!F3:F24,'Draft Board'!H3:H24,'Draft Board'!J3:J24,'Draft Board'!L3:L24,'Draft Board'!N3:N24,'Draft Board'!P3:P24,'Draft Board'!R3:R24,'Draft Board'!T3:T24,'Draft Board'!V3:V24,2,0)

This is supposed to lookup anyone on the draft board that has been selected and take them away from column A on the draft board.

 

Example-The draft board will show players left to be drafted.

Abbrederis,Jared|GBP|WR|

 

If I draft him or any player in column D then he is no longer shown in A, which is what I want.  If I use any of the other columns the player stays.

 

Can anyone tell me what I am missing?

Link to comment
Share on other sites

19 minutes ago, LAUNCH said:

Col A formula on the Draft Board......  =filter('Player Selection'!C1:C303,'Player Selection'!A1:A303="AVAILABLE")

Col A formula on the Player Selection....... =if(ISERROR(match(C2,'Draft Board'!D:D,0)),if(ISERROR(match(C2,'Draft Board'!F:F,0)),if(ISERROR(match(C2,'Draft Board'!H:H,0)),if(ISERROR(match(C2,'Draft Board'!J:J,0)),"AVAILABLE","DRAFTED"),"DRAFTED"),"DRAFTED"),"DRAFTED")

  • Like 1
Link to comment
Share on other sites

2 minutes ago, LAUNCH said:

That's awesome.  Thank you so much!

No problem.  Just a note though... you'll need to expand that out to the number of columns you want it to test.  I only went to J since I wasn't sure how deep you were going.

Link to comment
Share on other sites

I have a spreadsheet that I use to balance transactions on a daily basis.  Everyday I search for the word "core".  Is there a formula or button I can add to just click on to search for this word instead of the short cut of Cntl F and then typing in "Core"?

 

Link to comment
Share on other sites

21 minutes ago, dino259 said:

I have a spreadsheet that I use to balance transactions on a daily basis.  Everyday I search for the word "core".  Is there a formula or button I can add to just click on to search for this word instead of the short cut of Cntl F and then typing in "Core"?

 

What are you doing after you find "core"?

Link to comment
Share on other sites

35 minutes ago, dino259 said:

It is a long list of transactions that I can delete.  Ideally I wouldn't bring them into excel in the first place but that software does not let me exclude that tran code.

Turn on filters and then filter on that trans code.  Highlight all of the rows and delete entire rows.

Link to comment
Share on other sites

asking for my wife, I have no idea about excel....

 

she says something like when she does a vlook up, some values come up as #N/A.  she doesn't want this value and wants 0 or null or something like that, but not #N/A.

 

any advise?

 

thx

 

@Ned

Edited by Chemical X
Link to comment
Share on other sites

28 minutes ago, Chemical X said:

asking for my wife, I have no idea about excel....

 

she says something like when she does a vlook up, some values come up as #N/A.  she doesn't want this value and wants 0 or null or something like that, but not #N/A.

 

any advise?

 

thx

 

@Ned

=IFERROR(FORMULA,"0")

  • Like 1
Link to comment
Share on other sites

11 hours ago, Chemical X said:

can you elaborate?  where does this go?

She needs to put her VLOOKUP formula where @Ignoramus has the FORMULA noted.  This will return a 0 whenever her VLOOKUP returns an error.

One minor thing - she may need to exclude the quotes around the zero if she wants to be able to sum the column this formula will sit in.  Excel may treat that "0" as text instead of a number. 

  • Like 1
Link to comment
Share on other sites

1 hour ago, Ned said:

She needs to put her VLOOKUP formula where @Ignoramus has the FORMULA noted.  This will return a 0 whenever her VLOOKUP returns an error.

One minor thing - she may need to exclude the quotes around the zero if she wants to be able to sum the column this formula will sit in.  Excel may treat that "0" as text instead of a number. 

ahhhhh, now iUnderstand

Link to comment
Share on other sites

On August 4, 2016 at 0:49 PM, acarey50 said:
On August 4, 2016 at 0:28 PM, Ned said:

I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.micro

soft.com/en-us/kb/294683

This would be my suggestion as well. I primarily have used mail merge for creating mailing labels, but I don't see why you couldn't create a 4x6 layout with the fields you need and essentially get the same thing you are looking for.

The non-Excel way would be to use Access to do this, but that would be a bit more complicated than I think you need.

I know you have started, but I would look at Access for something like version 2.0. Once you figure out what you need, you can set up the system in Access in a way that you can pass it along to a less techie person. It is easy to set up so that people are inputing the data into easy to recognize and deal-with forms, with buttons to perform functions, instead of spreadsheets in Excel.

:2cents:

Link to comment
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

Link to comment
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.

Link to comment
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.

Link to comment
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.

 

 

Link to comment
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?

Link to comment
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,.

Link to comment
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.

Link to comment
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?

Link to comment
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
Link to comment
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
Link to comment
Share on other sites

  • 2 weeks later...

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!

 

Link to comment
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!!

 

 

Link to comment
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.

Link to comment
Share on other sites

  • 1 month later...

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
Link to comment
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.
×
  • Create New...