#DIV/0 error - don't want to hide the error, need to work around it

[RamJacCorp]

Excel novice warning! I'm trying to create a formula to calculate a selling price by dividing net cost by another number (the markup). This should display the Selling Price in the cell. The problem, of course, is that if there is no markup, the zero value yields the error. I found a fix to hide the error, but what I need the formula to do is test for a "0" value in the markup cell and if it finds one, pass the net cost cell contents into the selling price field. The reason is that the selling price field value is used in another area of the sheet. I hope I'm making some sense here. Thanks for any help you can offer!

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[FDibbins]

Hi, welcome to the forum

Will there actually be a 0 value, or will it be empty/blank?

0 value...
=if(A2=0,do-this,do-that)

blank cell...
=if(A2="",do-this,do-that)

This will cover both...
=if(OR(A2=0,A2=""),do-this,do-that)

Beyond that, Im not sure what you mean by "pass the net cost cell contents into the selling price field."?

[RamJacCorp]

Wow, thanks for the speedy help, TMS! I was over-complicating things, using ISERROR. Your solution is cleaner - thanks again.

[RamJacCorp]

Thanks for the welcome and the help, Ford! The markup cell can be either a zero value or blank, though I'll usually input zero if there's no markup. What I meant about passing on the net cost cell contents was that if the markup cell contained zero/blank value, whatever is in the net cost cell should be copied to the selling cost cell.

Trevor's suggestion seems to be working fine, but I'll fiddle around with your suggestions, as well. More than one way to skin a cat, huh?

[FDibbins]

Adapting Trevor's suggestion...
=IF(OR(markup=0,markup=""), net, net/markup)

[TMS]

@Ford:

Please Login or Register  to view this content.

will cope with either blank or zero, but not space, though I guess it doesn't hurt to be specific.

Regards, TMS

[TMS]

You're welcome. Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[RamJacCorp]

Done and done. Thanks again to both of you. Be prepared - I'll likely be asking for more help in the near future. Haven't used Excel in eons (self-taught), and am trying to finally learn the basics correctly.

[TMS]