- 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?
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?
- 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.
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.Brony said:I'm just playing with PowerBI, but looks pretty good for doing dashboards and very easy to share via Sharepoint. And pretty cheap too.
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 expand on my post above, here is a simple example to use conditional formatting.El Floppo said:- 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.
Does anyone know a good resource to learn more about the PowerPivot functions that are in Excel 2013?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.Brony said:I'm just playing with PowerBI, but looks pretty good for doing dashboards and very easy to share via Sharepoint. And pretty cheap too.
thanks.
Yes, that works too. I was just wondering why a csv file would not open already comma delimited on a double click.Jayrok, have you tried Data-> Text to Columns?
"Delimited" should already be marked, click next
Check box next to "Semicolon", click next
Finish
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.Yes, that works too. I was just wondering why a csv file would not open already comma delimited on a double click.Jayrok, have you tried Data-> Text to Columns?
"Delimited" should already be marked, click next
Check box next to "Semicolon", click next
Finish
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.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?
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.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.Yes, that works too. I was just wondering why a csv file would not open already comma delimited on a double click.Jayrok, have you tried Data-> Text to Columns?
"Delimited" should already be marked, click next
Check box next to "Semicolon", click next
Finish
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.
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).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.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.Yes, that works too. I was just wondering why a csv file would not open already comma delimited on a double click.Jayrok, have you tried Data-> Text to Columns?
"Delimited" should already be marked, click next
Check box next to "Semicolon", click next
Finish
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.
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.
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".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?
No. Bad example on my part. I guess to clear the confusion, ignore that last column.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".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?
You can get around it by doing something similar to what I suggested, but use the COUNTIF instead to build your key.No. Bad example on my part. I guess to clear the confusion, ignore that last column.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".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?
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.You can get around it by doing something similar to what I suggested, but use the COUNTIF instead to build your key.No. Bad example on my part. I guess to clear the confusion, ignore that last column.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".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?
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))
Assuming this is columns A and B on sheet1.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?
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.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
FIND is case sensitive, SEARCH is not.As an aside, what is the difference between SEARCH and FIND?
oooh, thank you. love it.FIND is case sensitive, SEARCH is not.As an aside, what is the difference between SEARCH and FIND?
Can't you just do a COUNTIF for PASSED as well?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 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't you just do a COUNTIF for PASSED as well?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 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.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't you just do a COUNTIF for PASSED as well?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?
Nevermind, I just figured it out.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.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't you just do a COUNTIF for PASSED as well?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?
Thank you Ned! That helps even more!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")"
Make a total column in your data and include it in your series data for the chart.Trying to combine 6 bars in a bar chart to make one bar using a secondary axis. HELP!