Fantasy Football - Footballguys Forums
Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.
***Official*** Excel Help Corner (1 Viewer)
- Thread starter Ned
- Start date
El Floppo
Footballguy
I don't use it for very tricky things- usually just make columns comparing contractor bids where I need to add it all up, take percentages that get added to final sum and take differences to compare the delta between different bids or between final bid and actual cost.
I'm on 2010... Couple of questions:
- When I take the difference (in a row across columns), I can't get it to read as a currency (dollar) amount even though my columns' sums do work as dollar amounts. I'd also like to automatically have the differences read visually distinct from eachother depending on whether they are + or -. As it is, I manually change the font.
- sometimes when selecting a tab, several tabs in the column get selected instead. I've done the workaround to zoom in and then back out to fix it- but that seems idiotic. is there a real way of fixing this?
Ned
Footballguy
- You can format your diff column to be a Currency format and select the red font as an option. https://support.content.office.net/en-us/media/0652251f-0209-40c6-ada9-438ea4a3f710.jpg
I don't use it for very tricky things- usually just make columns comparing contractor bids where I need to add it all up, take percentages that get added to final sum and take differences to compare the delta between different bids or between final bid and actual cost.
I'm on 2010... Couple of questions:
- When I take the difference (in a row across columns), I can't get it to read as a currency (dollar) amount even though my columns' sums do work as dollar amounts. I'd also like to automatically have the differences read visually distinct from eachother depending on whether they are + or -. As it is, I manually change the font.
- sometimes when selecting a tab, several tabs in the column get selected instead. I've done the workaround to zoom in and then back out to fix it- but that seems idiotic. is there a real way of fixing this?
- maybe you're drunk when this happens?
El Floppo
Footballguy
It's not working to set the sum for the row as a currency- I only get a number. Also not working to select the red font.- You can format your diff column to be a Currency format and select the red font as an option. https://support.content.office.net/en-us/media/0652251f-0209-40c6-ada9-438ea4a3f710.jpg
I don't use it for very tricky things- usually just make columns comparing contractor bids where I need to add it all up, take percentages that get added to final sum and take differences to compare the delta between different bids or between final bid and actual cost.
I'm on 2010... Couple of questions:
- When I take the difference (in a row across columns), I can't get it to read as a currency (dollar) amount even though my columns' sums do work as dollar amounts. I'd also like to automatically have the differences read visually distinct from eachother depending on whether they are + or -. As it is, I manually change the font.
- sometimes when selecting a tab, several tabs in the column get selected instead. I've done the workaround to zoom in and then back out to fix it- but that seems idiotic. is there a real way of fixing this?
- maybe you're drunk when this happens?
I'm absolutely drunk. but that problem is apparently a known problem- the zoom in and out fix was from the google. was hoping the experts in here would have a better solution.
Ned
Footballguy
- Any conditional formatting? Otherwise, it's going to be tough unless I can see an example.It's not working to set the sum for the row as a currency- I only get a number. Also not working to select the red font.- You can format your diff column to be a Currency format and select the red font as an option. https://support.content.office.net/en-us/media/0652251f-0209-40c6-ada9-438ea4a3f710.jpg
I don't use it for very tricky things- usually just make columns comparing contractor bids where I need to add it all up, take percentages that get added to final sum and take differences to compare the delta between different bids or between final bid and actual cost.
I'm on 2010... Couple of questions:
- When I take the difference (in a row across columns), I can't get it to read as a currency (dollar) amount even though my columns' sums do work as dollar amounts. I'd also like to automatically have the differences read visually distinct from eachother depending on whether they are + or -. As it is, I manually change the font.
- sometimes when selecting a tab, several tabs in the column get selected instead. I've done the workaround to zoom in and then back out to fix it- but that seems idiotic. is there a real way of fixing this?
- maybe you're drunk when this happens?
I'm absolutely drunk. but that problem is apparently a known problem- the zoom in and out fix was from the google. was hoping the experts in here would have a better solution.
- I've never seen anything like that happen before.
El Floppo
Footballguy
Donkey Derp
Footballguy
Conditional Formatting is what it sounds like - you can tell Excel to format a cell depending on different conditions. For example, I can write a formula that says "if the number in this cell is less than 0, highlight it red. If it is equal to 0, highlight it blue, If it's greater than 0, highlight it green". It works with colors, borders, cell type, and fonts. And as you change the value in that cell, the formatting will change for you automatically.El Floppo said:
To check if this is being used, click the cell you want to look at. Under home, there is a "Conditional Formatting" icon. Click "Manage Rules". You will see something under Rule (applied in order shown) if there is something set up. If the white box is blank, there is no CF set up.
Donkey Derp
Footballguy
To expand on my post above, here is a simple example to use conditional formatting.El Floppo said:
To practice, open up a new workbook and type in any number in cell A1.
Go to Home -> Conditional Formatting -> Manage Rules -> New Rule -> Use a formula to determine which cells to format
Let's say we want + to be green and - to be red.
Under "Format values where the formula is true", type =IF(A1>0,"TRUE","FALSE")
Click the "Format..." button -> Fill -> Click color green -> Click Ok
Click "New Rule" again
Now you will type =IF(A1<0,"TRUE","FALSE")
Click the "Format..." button -> Fill -> Click color RED -> Click Ok
Now under the "Conditional Formatting Rules Manager" you should see two separate formulas.
Click Ok. Mess around with the numbers in cell A1. >0 should be green, <0 should be red, 0 will have no format.
If you are good with IF statements, you should be able to modify the IF statement as you need it. Just use the "TRUE" and "FALSE" as I did to set up CF.
To copy this conditional formatting to other cells, make sure to only go to Paste Special -> Formats. Otherwise, everything gets copied.
/tangent
ALT key is a good shortcut to use.
If you hit ALT, you will see small square letters pop up under each ribbon option ... "F" for File, "H" for home, "1" for save etc
The shortcut for Paste Special -> Formats is "ALT", "E", "S", "T"
Gawain
Footballguy
Does anyone know a good resource to learn more about the PowerPivot functions that are in Excel 2013?
Seems to be the Office answer to some of these third party programs and if anyone knows of good tutorial sites I'd be interested.
Jayrok
Footballguy
When a csv file isn't a csv file. When using a new software program, X, there is a feature link to "export as a csv file" on pages so one can export data logs. I want to export a log but the file is not comma delimited by default. It is semicolon delimited. Isn't that a european standard?
Double clicking the .csv file throws it into excel as a semicolon mess. I can import the file into excel but have to select semicolon so it will format properly. I can also save the file in notepad and use find/replace to change all the semicolons into commas.. then double clicking the file opens into excel nicely formatted.
I checked my computer's regional settings and verified it is US and numbers separator is a "comma". Is it possible the software developer didn't code it correctly to actually export the data as a .csv file?
Or is this something I need to do on my end?
Double clicking the .csv file throws it into excel as a semicolon mess. I can import the file into excel but have to select semicolon so it will format properly. I can also save the file in notepad and use find/replace to change all the semicolons into commas.. then double clicking the file opens into excel nicely formatted.
I checked my computer's regional settings and verified it is US and numbers separator is a "comma". Is it possible the software developer didn't code it correctly to actually export the data as a .csv file?
Or is this something I need to do on my end?
Donkey Derp
Footballguy
Jayrok, have you tried Data-> Text to Columns?
"Delimited" should already be marked, click next
Check box next to "Semicolon", click next
Finish
"Delimited" should already be marked, click next
Check box next to "Semicolon", click next
Finish
Donkey Derp
Footballguy
Sorry, looks like you already knew that.
Jayrok
Footballguy
Yes, that works too. I was just wondering why a csv file would not open already comma delimited on a double click.
It sounds like it does open as comma-delimited on a double-click. It's just that there aren't any commas in it to delimit anything.
I think it would be asking too much of Excel to look at the file and decide on its own whether it's comma-delimited or semicolon-delimited. Try to get X to output to a true .csv file with commas, not with semicolons. Barring that, either the notepad thing or the text-to-columns thing would seem like the easiest solution.
Donkey Derp
Footballguy
From what I recall, .csv extension is really just a blanket name. I don't think it's supposed to have separation via comma even though one would easily assume that from the acronym.
I suppose it's kind of like asking for a Kleenex when referring to any tissue. .csv can be any type of separation format and not necessarily only commas.
I suppose it's kind of like asking for a Kleenex when referring to any tissue. .csv can be any type of separation format and not necessarily only commas.
Ned
Footballguy
Our developers drive me nuts with this. When I save the file to my PC, I save it as .txt and then open it from Excel. That will prompt the text wizard where you can choose your own delimiter.
Or you can also put "sep=;" as the first line of your document via notepad.
Jayrok
Footballguy
This is what I'd like to do but it doesn't allow me the option. I can only open it, save it, or save as... I pick save as and save it as an excel .csv file and it saves it with the semicolons. Maybe the software coder of X is the one who can make this change (for it to save as a true .csv file) as part of the next version release.
The other thing that is frustrating about X and the export link is that it only exports the current page. So instead of exporting 700 lines (or 40 pages) of the log file into excel, it will only export the 10 lines that are showing on the current page.
Van Dyman
Footballguy
If there are any quoted text fields, do not use the notepad find/replace trick...because that quoted text might contain legitimate semi-colons that are truly part of the data (not actual separators).
If your data could potentially contain actual commas and the application is unable to properly quote text fields, this may explain why the developer made the bone-headed decision to use semicolons.
Just on a whim, it might be worth checking the application's settings to see if there are any options for controlling the column separator for this CSV output.
ETA: Ideally, you don't want to have to muck around in Excel with this every time you open one of these files. Excel isn't doing something wrong. Ask the developer to fix it. He may be hesitant if other users have automated processes that already depend on the semicolon column separator. If so, ask the developer to add an option to the application so that you can use it your way while other users continue to use the default column separator (semicolon, apparently).
Last edited by a moderator:
TheIronSheik
SUPER ELITE UPPER TIER
I feel like there's an easy answer to this, but for some reason I'm having trouble finding it. I'm sure I could get what I wanted by writing a huge formula or two to get what I want, but before I do that I figured I'd check here for an easy answer.
When using VLOOKUP, I run into two lines having the same value. How can I get the VLOOKUP formula to find the next value in the next line? Here's an example:
RED 35 AAABLUE 40 ABAYELLOW 42 ACABLUE 45 ADA So if I do a VLOOKUP for BLUE, I'd get back 40. But in the next line, I'd want to see 45 show up. Is this possible? Did I explain it well enough, also?
When using VLOOKUP, I run into two lines having the same value. How can I get the VLOOKUP formula to find the next value in the next line? Here's an example:
RED 35 AAABLUE 40 ABAYELLOW 42 ACABLUE 45 ADA So if I do a VLOOKUP for BLUE, I'd get back 40. But in the next line, I'd want to see 45 show up. Is this possible? Did I explain it well enough, also?
Ned
Footballguy
Are the values in col C always unique? If so, you can build a key in column D where you combine both A and C. So in the example, you'd lookup against "BLUEADA".
TheIronSheik
SUPER ELITE UPPER TIER
Ned
Footballguy
You can get around it by doing something similar to what I suggested, but use the COUNTIF instead to build your key.
In column D, put the below formula (make sure to start at the top and autofill - the $ is important). Then you can lookup using col A and the count. So in this example, you'd want to lookup "BLUE2".
=CONCATENATE(A1,COUNTIF(A$1:A1,A1))
TheIronSheik
SUPER ELITE UPPER TIER
TheIronSheik
SUPER ELITE UPPER TIER
Spin
Footballguy
Assuming this is columns A and B on sheet1.
On sheet2 you have A1 = Blue, A2 = Blue.. etc.
Put this in B1 and copy/paste down.
=IFERROR(INDEX(Sheet1!$A$1:$B$4,SMALL(IF(IFERROR(SEARCH($A1,Sheet1!$A$1:$A$4),"False"),ROW(Sheet1!$B$1:$B$4)),ROW(1:1)),2),"")
It's an array formula, so make sure you hold CTRL + Shift when you hit enter.
Sheet1 for this small demo:
http://puu.sh/iuVtF/385905ea55.png
This is what my sheet2 looks like for your small demo.
http://puu.sh/iuVsA/0635ac2556.png
Let me know if you have any questions.
Last edited by a moderator:
Nick Vermeil
Footballguy
Dot
Spin
Footballguy
Sheik,
It dawned on me that you may have multiple colors in the page, so I modified the formula to handle multiple colors within each column.
If you're going to have anything other then just Blue within the column, use this formula instead;
=IFERROR(INDEX(Sheet1!$A$1:$B$10,SMALL(IF(IFERROR(SEARCH($A1,Sheet1!$A$1:$A$10),"False"),ROW(Sheet1!$B$1:$B$10)),COUNTIF($A$1:A1,A1)),2),"")
Using ctrl + shift + enter of course since it's an array formula.
A list like this on sheet 1:
http://puu.sh/iuYXm/c887055000.png
Would come out like this on sheet 2:
http://puu.sh/iuYYZ/2f4a500fc3.png
It dawned on me that you may have multiple colors in the page, so I modified the formula to handle multiple colors within each column.
If you're going to have anything other then just Blue within the column, use this formula instead;
=IFERROR(INDEX(Sheet1!$A$1:$B$10,SMALL(IF(IFERROR(SEARCH($A1,Sheet1!$A$1:$A$10),"False"),ROW(Sheet1!$B$1:$B$10)),COUNTIF($A$1:A1,A1)),2),"")
Using ctrl + shift + enter of course since it's an array formula.
A list like this on sheet 1:
http://puu.sh/iuYXm/c887055000.png
Would come out like this on sheet 2:
http://puu.sh/iuYYZ/2f4a500fc3.png
Last edited by a moderator:
TheIronSheik
SUPER ELITE UPPER TIER
I'll have to take a deeper look at this next week. I am familiar with array formulas, but I don't really know how they work.
Appreciate the response.
Long Ball Larry
Footballguy
As an aside, what is the difference between SEARCH and FIND?
Spin
Footballguy
Instinctive
Footballguy
:dot:
Long Ball Larry
Footballguy
ragincajun
Footballguy
Let's talk Fuzzy Lookup.
I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.
Example:
Whse 1: pen, red, bic
Whse 2: bic pen
Whse 3: pen model #abcd
The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.
thoughts?
I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.
Example:
Whse 1: pen, red, bic
Whse 2: bic pen
Whse 3: pen model #abcd
The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.
thoughts?
Hastur
Footballguy
OK Excel experts, here is the challenge:
I have 3 columns, 15 rows deep. Each cell has either 'PASS' or 'FAIL'. They are according to rows, with 3 different 'opportunities' to fail that row.
I.E.
PASS PASS PASS
PASS PASS FAIL
FAIL PASS PASS
PASS PASS PASS
FAIL FAIL FAIL
PASS PASS PASS
PASS FAIL PASS
PASS FAIL FAIL
PASS PASS PASS
FAIL PASS FAIL
PASS PASS PASS
PASS PASS PASS
PASS PASS FAIL
FAIL PASS PASS
PASS PASS PASS
So, there are 15 total rows, with 8 of them failing. Make sense so far?
I used a simple COUNTIF statement to find how many rows failed: =COUNTIF(A1:A3, "fail")
Did that down the line for all 15 rows, and have the following underneath the rows:
Passed: 7
Failed: 8
However, there are times not all 15 rows will have values(passed/failed). How can I get the 'Passed' counter to not count the 0's? There is a way to make things invisible if no value is detected, yes?
I have 3 columns, 15 rows deep. Each cell has either 'PASS' or 'FAIL'. They are according to rows, with 3 different 'opportunities' to fail that row.
I.E.
PASS PASS PASS
PASS PASS FAIL
FAIL PASS PASS
PASS PASS PASS
FAIL FAIL FAIL
PASS PASS PASS
PASS FAIL PASS
PASS FAIL FAIL
PASS PASS PASS
FAIL PASS FAIL
PASS PASS PASS
PASS PASS PASS
PASS PASS FAIL
FAIL PASS PASS
PASS PASS PASS
So, there are 15 total rows, with 8 of them failing. Make sense so far?
I used a simple COUNTIF statement to find how many rows failed: =COUNTIF(A1:A3, "fail")
Did that down the line for all 15 rows, and have the following underneath the rows:
Passed: 7
Failed: 8
However, there are times not all 15 rows will have values(passed/failed). How can I get the 'Passed' counter to not count the 0's? There is a way to make things invisible if no value is detected, yes?
Last edited by a moderator:
TheIronSheik
SUPER ELITE UPPER TIER
Hastur
Footballguy
I did, but the overall Pass total will read the empty rows as passed. So, lets say I have a worksheet with 15 rows, but only 8 of the rows have values(passed/failed). The passed total at the bottom is currently reading the rows that are all pass, PLUS the blank rows(rows 9 thru 15).
Ned
Footballguy
Can you put your example in a google sheet or something? I don't see how that would be happening, so I think we're all missing something in your explanation.
Spin
Footballguy
Put this in E2:
=IFERROR(HLOOKUP("FAIL",B2
2,1,0),"PASS")
And drag down.
A bit simpler to modify then the nested ifs. But Ned's works just as easily. However, if you ever need to add a 4th or 5th column, or even drop down to two, just change the range of the Hlookup, instead of having to mess with the nested ifs.
=IFERROR(HLOOKUP("FAIL",B2
2,1,0),"PASS")And drag down.
A bit simpler to modify then the nested ifs. But Ned's works just as easily. However, if you ever need to add a 4th or 5th column, or even drop down to two, just change the range of the Hlookup, instead of having to mess with the nested ifs.
Ned
Footballguy
Make a total column in your data and include it in your series data for the chart.
Right click the new total bar - Format Data Series...change it to a secondary axis (how this is displayed is different in 2010, IIRC).
