Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

Using solver, if I want to pull from a specific list of values, how would I do that?

Specifically, I'm trying to choose an optimal Daily Fantasy Baseball Lineup based on the player projections for the day. (I realize that it ultimately may not be the best way to do it, but I want to play around with it.

Set up a binary column and use it as the decision variable ("by changing" cells) in Solver.

Edited by wdcrob

Share this post


Link to post
Share on other sites

Do any of you Excel guys use third party software, such as BIME, Tableau, Qlikview or something else?

Does anyone have a recommendation?

While the visual representations offered by these companies are amazing, I need something more basic.

I'm looking for a way to upload raw sales data (either retail register sales or my sales to customers) into a static dashboard that will then create reports automatically and send them off to the proper folks (reorders for production, sales figures for execs, overstocks and movements for logistics).

The end output can be in excel or in a proprietary file, but it needs to be able to be manipulated.

Share this post


Link to post
Share on other sites

My company uses a product called xTraction to do pretty much what you're asking. Create dashboards, automatically email reports etc.

Share this post


Link to post
Share on other sites

I'm just playing with PowerBI, but looks pretty good for doing dashboards and very easy to share via Sharepoint. And pretty cheap too.

Share this post


Link to post
Share on other sites

:blackdot:

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?

Share this post


Link to post
Share on other sites

:blackdot:

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?

- 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

- maybe you're drunk when this happens?

Share this post


Link to post
Share on other sites

:blackdot:

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?

- 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

- maybe you're drunk when this happens?

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.

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.

Share this post


Link to post
Share on other sites

:blackdot:

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?

- 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

- maybe you're drunk when this happens?

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.

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.

- Any conditional formatting? Otherwise, it's going to be tough unless I can see an example.

- I've never seen anything like that happen before.

Share this post


Link to post
Share on other sites

dawt

Share this post


Link to post
Share on other sites

I'm just playing with PowerBI, but looks pretty good for doing dashboards and very easy to share via Sharepoint. And pretty cheap too.

What systems/data sources are you working with in PowerBI? I just looked it up based on your post and see it claims to work nicely with Salesforce which I would like and i'd expect it to couple well with Microsoft AX on the GL side.

thanks.

Share this post


Link to post
Share on other sites

I don't know what conditional formatting is.

multiple select

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.

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.

Share this post


Link to post
Share on other sites

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

To expand on my post above, here is a simple example to use conditional formatting.

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"

Share this post


Link to post
Share on other sites

Fantastic stuff, derp. Thanks! :thumbup:

Will look at it when I get to the office

Share this post


Link to post
Share on other sites

I'm just playing with PowerBI, but looks pretty good for doing dashboards and very easy to share via Sharepoint. And pretty cheap too.

What systems/data sources are you working with in PowerBI? I just looked it up based on your post and see it claims to work nicely with Salesforce which I would like and i'd expect it to couple well with Microsoft AX on the GL side.

thanks.

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Jayrok, have you tried Data-> Text to Columns?

"Delimited" should already be marked, click next

Check box next to "Semicolon", click next

Finish

Share this post


Link to post
Share on other sites

Jayrok, have you tried Data-> Text to Columns?

"Delimited" should already be marked, click next

Check box next to "Semicolon", click next

Finish

Yes, that works too. I was just wondering why a csv file would not open already comma delimited on a double click.

Share this post


Link to post
Share on other sites

Jayrok, have you tried Data-> Text to Columns?

"Delimited" should already be marked, click next

Check box next to "Semicolon", click next

Finish

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

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.

Share this post


Link to post
Share on other sites

Jayrok, have you tried Data-> Text to Columns?

"Delimited" should already be marked, click next

Check box next to "Semicolon", click next

Finish

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.

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.

Share this post


Link to post
Share on other sites

Jayrok, have you tried Data-> Text to Columns?

"Delimited" should already be marked, click next

Check box next to "Semicolon", click next

Finish

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.

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.

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

Edited by VandyMan

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

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

Share this post


Link to post
Share on other sites

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?

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

No. Bad example on my part. I guess to clear the confusion, ignore that last column.

Share this post


Link to post
Share on other sites

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?

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

No. Bad example on my part. I guess to clear the confusion, ignore that last column.

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

Share this post


Link to post
Share on other sites

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?

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

No. Bad example on my part. I guess to clear the confusion, ignore that last column.

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

Yeah, that's what I meant when I said I could do a huge formula or two formulas. I knew there was a workaround that I could do, and that was my initial plan, but I thought maybe I just didn't know of an easier way. One of those things where it's so easy, I just overlook it.

Share this post


Link to post
Share on other sites

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?

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.

Edited by Spin

Share this post


Link to post
Share on other sites

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

Edited by Spin
  • Like 1

Share this post


Link to post
Share on other sites

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

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.

Share this post


Link to post
Share on other sites

As an aside, what is the difference between SEARCH and FIND?

FIND is case sensitive, SEARCH is not.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Edited by Hastur

Share this post


Link to post
Share on other sites

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?

Can't you just do a COUNTIF for PASSED as well?

Share this post


Link to post
Share on other sites

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?

Can't you just do a COUNTIF for PASSED as well?

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

Share this post


Link to post
Share on other sites

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?

Can't you just do a COUNTIF for PASSED as well?

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

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.

Share this post


Link to post
Share on other sites

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?

Can't you just do a COUNTIF for PASSED as well?

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

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.

Nevermind, I just figured it out.

Thanks though!

Edited by Hastur

Share this post


Link to post
Share on other sites

In E2 put "=IF(AND(B2="Pass",C2="pass",D2="pass"),"pass",IF(OR(B2="",C2="",D2=""),"","fail"))" and fill down.

In C18 put "=COUNTIF(E2:E16,"pass")"

In C19 put "=COUNTIF(E2:E16,"fail")"

Share this post


Link to post
Share on other sites

In E2 put "=IF(AND(B2="Pass",C2="pass",D2="pass"),"pass",IF(OR(B2="",C2="",D2=""),"","fail"))" and fill down.

In C18 put "=COUNTIF(E2:E16,"pass")"

In C19 put "=COUNTIF(E2:E16,"fail")"

Thank you Ned! That helps even more!

I appreciate it!

  • Like 1

Share this post


Link to post
Share on other sites

Put this in E2:

=IFERROR(HLOOKUP("FAIL",B2:D2,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.

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.