Posted: 2/10/2015 6:06:50 PM EDT
|
The power of Arfom compels me, the power of Arfcom compels me ...
OK. The issue is I have a decent (at best) ability with Excel. I have tasked with trying to create a simple goal tracking program for monthly sales. I have tried to adapt an old sheet someone else created and update to today. My issue is I have conditional formatting for some things to highlight certain levels, but I'd really like for cells with formulas to not show up like the pic below. 
I would much rather it look like this where formulas that don't have data to execute from the "white" cells to the left would show something like a "-" so not to skew the data. See below for example. 
So any ideas and or suggestions on how to get this to happen? I'm sure it easy, but I admit my limitations. |
|
3 ways to hide zero's
For the negative percentage number use a if statement =if(cell(cn:rn)<=0,0,your regular formula) , cell(cn:rn) = one of your data entry cells - if its zero or negative then you don't want to do the calculation. then by hiding 'zeros' you should get an empty cell |
|
Something along these lines should do it: =IF(Y=0,0,X/Y) Basically, if your denominator is a zero you get a zero and if the denominator is non-zero you get the calculated result. ETA - if you want your zero's displayed differently (or not at all), refer to the first reply for options. |
|
Quoted:
My answer to these is always "This bullshit belongs in a relational database, fuck spreadsheets". 
I never said it was helpful advice.  still trying to understand how to use a database. I get the idea - I just have no idea where to start nor how to write the data evaluation instructions. |
|
Quoted:
The power of Arfom compels me, the power of Arfcom compels me ... OK. The issue is I have a decent (at best) ability with Excel. I have tasked with trying to create a simple goal tracking program for monthly sales. I have tried to adapt an old sheet someone else created and update to today. My issue is I have conditional formatting for some things to highlight certain levels, but I'd really like for cells with formulas to not show up like the pic below. http://i1221.photobucket.com/albums/dd471/stubrojo/Sales%20Sample%201_zpsdp5wtbx8.png I would much rather it look like this where formulas that don't have data to execute from the "white" cells to the left would show something like a "-" so not to skew the data. See below for example. http://i1221.photobucket.com/albums/dd471/stubrojo/Sales%20Sample%202_zps28yiseyo.png So any ideas and or suggestions on how to get this to happen? I'm sure it easy, but I admit my limitations. Try this.. =IF(ISNUMBER(H3-I3),H3-I3,"-") Assuming your screenshots use the same conditional formulas on both of them, then just paste something like that into the cell with the computed value and that should do it. |
|
Quoted: still trying to understand how to use a database. I get the idea - I just have no idea where to start nor how to write the data evaluation instructions. Quoted: Quoted: My answer to these is always "This bullshit belongs in a relational database, fuck spreadsheets". I never said it was helpful advice. still trying to understand how to use a database. I get the idea - I just have no idea where to start nor how to write the data evaluation instructions. The correct answer to that riddle is to hire a DB developer. If you need a database for more than recipes or addresses, you don't have time to build it. I know, I've dabbled in DB development and it is not time effective. It's not something you just learn as you go if you have other work to do. |
|
Quoted: Try this.. Assuming your screenshots use the same conditional formulas on both of them, then just paste something like that into the cell with the computed value and that should do it. Quoted: Quoted: The power of Arfom compels me, the power of Arfcom compels me ... OK. The issue is I have a decent (at best) ability with Excel. I have tasked with trying to create a simple goal tracking program for monthly sales. I have tried to adapt an old sheet someone else created and update to today. My issue is I have conditional formatting for some things to highlight certain levels, but I'd really like for cells with formulas to not show up like the pic below. http://i1221.photobucket.com/albums/dd471/stubrojo/Sales%20Sample%201_zpsdp5wtbx8.png I would much rather it look like this where formulas that don't have data to execute from the "white" cells to the left would show something like a "-" so not to skew the data. See below for example. http://i1221.photobucket.com/albums/dd471/stubrojo/Sales%20Sample%202_zps28yiseyo.png So any ideas and or suggestions on how to get this to happen? I'm sure it easy, but I admit my limitations. Try this.. =IF(ISNUMBER(H3-I3),H3-I3,"-") Assuming your screenshots use the same conditional formulas on both of them, then just paste something like that into the cell with the computed value and that should do it. Limit your input, control your output. |
|
First off ... Arfcom never disappoints. Thanks for all the suggestions and help!
Got everthing set except column G (Paid %). Is there anyway to hide the #DIV/0!? That formula right now is set at =f4/h4 The problem is of course, those rows are not completed for the entire year and it give the error above. I have tried some of the =if formulas listed above but can't get them to work. Any help on these would be great. I got the other formulas to work and it looks just about perfect. |
|
Quoted:
My answer to these is always "This bullshit belongs in a relational database, fuck spreadsheets".
I never said it was helpful advice. No. That is how unwieldy Access monstrosities begin. They always start as a simple thing like that, and before you know it, they are crossing departments pulling from lord knows what kind of SQL DB's, and updating indiscriminately.
Access should be deleted from MS Office. 
Just kidding, but a large part of my new job is dealing with an Access monstrosity parked on SQL. 650 pages of code. a dozen or so remote users, manually updating via subscription/subscriber. It's a thing of beauty, and it started as an excel spreadsheet. As you can imagine, the administrative overhead is way larger than it needs to be. Now it runs an entire companies remote sales team. 
ETA: NEVER and I mean NEVER mention Access skills on a resume. EVAR!!11!!!! |
|
Quoted:
What are you trying to do exactly? You can use ISBLANK() or IF() to take care of this. For example: =IF(OR(ISBLANK(D2),D2=0),"-",D2/C2) ...would test cell D2 for either being blank or zero. If it is, you get a dash, if not, you get the value of D2/C2. This |
|
Quoted:
First off ... Arfcom never disappoints. Thanks for all the suggestions and help! Got everthing set except column G (Paid %). Is there anyway to hide the #DIV/0!? That formula right now is set at =f4/h4 The problem is of course, those rows are not completed for the entire year and it give the error above. I have tried some of the =if formulas listed above but can't get them to work. Any help on these would be great. I got the other formulas to work and it looks just about perfect. =IF(H4=0,"-",f4/h4) Alternatively =IF(H4>0,f4/h4,"-") |
|
Quoted:
My answer to these is always "This bullshit belongs in a relational database, fuck spreadsheets".
I never said it was helpful advice. I agree, the nice thing about Excel is that it doesn't require a business to spend more money and most people can figure out the basics of how to use it. It's relatively easy to use and has the ability to do quite a bit once someone learns the basics. I've written entire applications in Excel to do things, because it was literally the only thing I had available to me that I could write code in without going through an essentially impossible certification process and loading additional software (that would never be approved) onto the machines. |
|
Quoted:
No. That is how unwieldy Access monstrosities begin. They always start as a simple thing like that, and before you know it, they are crossing departments pulling from lord knows what kind of SQL DB's, and updating indiscriminately.
Access should be deleted from MS Office.
Just kidding, but a large part of my new job is dealing with an Access monstrosity parked on SQL. 650 pages of code. a dozen or so remote users, manually updating via subscription/subscriber. It's a thing of beauty, and it started as an excel spreadsheet. As you can imagine, the administrative overhead is way larger than it needs to be. Now it runs an entire companies remote sales team.
ETA: NEVER and I mean NEVER mention Access skills on a resume. EVAR!!11!!!! Quoted:
Quoted:
My answer to these is always "This bullshit belongs in a relational database, fuck spreadsheets".
I never said it was helpful advice. No. That is how unwieldy Access monstrosities begin. They always start as a simple thing like that, and before you know it, they are crossing departments pulling from lord knows what kind of SQL DB's, and updating indiscriminately.
Access should be deleted from MS Office.
Just kidding, but a large part of my new job is dealing with an Access monstrosity parked on SQL. 650 pages of code. a dozen or so remote users, manually updating via subscription/subscriber. It's a thing of beauty, and it started as an excel spreadsheet. As you can imagine, the administrative overhead is way larger than it needs to be. Now it runs an entire companies remote sales team.
ETA: NEVER and I mean NEVER mention Access skills on a resume. EVAR!!11!!!! I'm pretty sure he didn't say Access... When I say database I never mean Access, that thing is an abomination that should have never been released. |
|
Quoted:
First off ... Arfcom never disappoints. Thanks for all the suggestions and help! Got everthing set except column G (Paid %). Is there anyway to hide the #DIV/0!? That formula right now is set at =f4/h4 The problem is of course, those rows are not completed for the entire year and it give the error above. I have tried some of the =if formulas listed above but can't get them to work. Any help on these would be great. I got the other formulas to work and it looks just about perfect. You can make that show whatever you want by using the IFERROR formula. For example =IFERROR(f4/H4,"-") will show a dash in that cell until the year completes. You can also get fancy and use SUMIF formulas to calculate that for YTD Paid %) which I'm sure people may want to know at some point this year. IFERROR is a great way to make unsightly #REF! and #DIV0! errors "disappear". |
|
Quoted:
I'm pretty sure he didn't say Access... When I say database I never mean Access, that thing is an abomination that should have never been released. Quoted:
Quoted:
Quoted:
My answer to these is always "This bullshit belongs in a relational database, fuck spreadsheets".
I never said it was helpful advice. No. That is how unwieldy Access monstrosities begin. They always start as a simple thing like that, and before you know it, they are crossing departments pulling from lord knows what kind of SQL DB's, and updating indiscriminately.
Access should be deleted from MS Office.
Just kidding, but a large part of my new job is dealing with an Access monstrosity parked on SQL. 650 pages of code. a dozen or so remote users, manually updating via subscription/subscriber. It's a thing of beauty, and it started as an excel spreadsheet. As you can imagine, the administrative overhead is way larger than it needs to be. Now it runs an entire companies remote sales team.
ETA: NEVER and I mean NEVER mention Access skills on a resume. EVAR!!11!!!! I'm pretty sure he didn't say Access... When I say database I never mean Access, that thing is an abomination that should have never been released. I was looking at it from the point of view of a guy sitting in excel being told "put it in a database". You know they aren't thinking SQL! |
|
Quoted:
Once again, I can't say this enough ... Arfcom delivers! Thanks to everyone who helped. Anybody who didn't tell you to put in MS SQL, or PostgreSQL or shit...even MYSQL, did you no favors.
They've cursed you. And you know what else? They've cursed an entire department that will no doubt use the horrible spreadsheet you've needlessly created, and make it business critical. No sir, you should be flaming these people with all your might, for the harm they've caused you two years before you've even realized it.
|
|
Quoted:
Anybody who didn't tell you to put in MS SQL, or PostgreSQL or shit...even MYSQL, did you no favors.
They've cursed you. And you know what else? They've cursed an entire department that will no doubt use the horrible spreadsheet you've needlessly created, and make it business critical. No sir, you should be flaming these people with all your might, for the harm they've caused you two years before you've even realized it. Quoted:
Quoted:
Once again, I can't say this enough ... Arfcom delivers! Thanks to everyone who helped. Anybody who didn't tell you to put in MS SQL, or PostgreSQL or shit...even MYSQL, did you no favors.
They've cursed you. And you know what else? They've cursed an entire department that will no doubt use the horrible spreadsheet you've needlessly created, and make it business critical. No sir, you should be flaming these people with all your might, for the harm they've caused you two years before you've even realized it. truth.... |