|
|
Post by WhutScreenName on Jul 17, 2024 13:32:09 GMT
I have a spreadsheet where I place daily sales numbers and then average the past 3 weeks with the current week to show deviation (positive or negative) for the current week. It allows me to catch unusual drops or increases in output. I'm running into an issue where some of the accounts I monitor, are not selling anything, and therefore a "0" (zero) is being placed in the cell. This causes a "#DIV/0!" to show up in the deviation cell since it's not possible to divide by zero. HOW can I convert that to be something other than the error message? I don't care if it says "NA" or "0", I just don't want the error message. I've looked online, but not finding anything that works. Any help would be appreciated.
I doubt this will help, but in case it does, here's the formula I'm using =(C165-AVERAGE(C158,C151,C144))/C165 If any of those cells are 0, that's when I get the 'error message'
|
|
|
|
Post by the light works on Jul 17, 2024 13:54:30 GMT
I have a spreadsheet where I place daily sales numbers and then average the past 3 weeks with the current week to show deviation (positive or negative) for the current week. It allows me to catch unusual drops or increases in output. I'm running into an issue where some of the accounts I monitor, are not selling anything, and therefore a "0" (zero) is being placed in the cell. This causes a "#DIV/0!" to show up in the deviation cell since it's not possible to divide by zero. HOW can I convert that to be something other than the error message? I don't care if it says "NA" or "0", I just don't want the error message. I've looked online, but not finding anything that works. Any help would be appreciated. I doubt this will help, but in case it does, here's the formula I'm using =(C165-AVERAGE(C158,C151,C144))/C165 If any of those cells are 0, that's when I get the 'error message' I'm not at all skilled with digital spreadsheets, and don't know how to do advanced functions; so assuming C165 is your current week, and C150, 151, and 144 are the three previous weeks, my formula would be more like C165-((C158+C151+c144)/3) and that would give an absolute change between the previous 3 weeks and the current week. it looks like you're aiming for a percentage change, and that will have you dividing by zero any time you have a zero in the wrong cell. I guess if you wanted a percentage, you could write your formula to add .0001 to every cell before calculating the deviation. it would be small enough to give you a reasonably accurate percentage deviation, but prevent a div/0 error. at least to my level of math skills. I.E. ((C165+.0001)-average((c158+.0001),(C151+.0001),(C144+.001)))/(C165+.0001) |
|
|
|
Post by WhutScreenName on Jul 17, 2024 16:36:42 GMT
Thank you for your idea, it pointed me in a direction I hadn't considered before. I was able to get it to work doing a combination of what you suggested, and some additional tweaking. That said, I wouldn't have gotten there without your assistance.
|
|
|
|
Post by GTCGreg on Jul 17, 2024 18:09:39 GMT
Try this
=IFERROR((C165-AVERAGE(C158,C151,C144))/C165,"")
You should get a blank cell instead of the error message. Or you could put "NA" between the quotes at the end and then NA should appear in a cell instead of the the error message.
|
|
|
|
Post by the light works on Jul 17, 2024 19:26:25 GMT
Thank you for your idea, it pointed me in a direction I hadn't considered before. I was able to get it to work doing a combination of what you suggested, and some additional tweaking. That said, I wouldn't have gotten there without your assistance. sometimes not knowing what you're doing lets you see something professionals don't. |
|
|
|
Post by WhutScreenName on Jul 17, 2024 19:35:18 GMT
Try this =IFERROR((C165-AVERAGE(C158,C151,C144))/C165,"") You should get a blank cell instead of the error message. Or you could put "NA" between the quotes at the end and then NA should appear in a cell instead of the the error message. I had the IFERROR in the wrong spot on my attempts. That does what I wanted! |
|
|
|
Post by GTCGreg on Jul 17, 2024 21:18:02 GMT
Try this =IFERROR((C165-AVERAGE(C158,C151,C144))/C165,"") You should get a blank cell instead of the error message. Or you could put "NA" between the quotes at the end and then NA should appear in a cell instead of the the error message. I had the IFERROR in the wrong spot on my attempts. That does what I wanted! Yeah, that's the problem with those Excel formulas. You leave out a parenthesis or put a comma in the wrong place and you're screwed. And often those types of errors are had to spot. |
|
|
|
Post by wvengineer on Jul 18, 2024 0:31:38 GMT
Yeah, I use the IfError command a lot.
I find that and a vlookup/hlookup as the of my go-to commands.
|
|
