markup vs. margin

[Hank Moore]

Novice user here. I would like to be able to plug in a desired margin and/or markup % and show sell price. In conjunction want to show actual margin and/or markup % based on what input is.
So:
A2 = Cost
B2 = Desired Markup
C2 = Desired Margin
D2 = Sell Price
E2 = Actual Markup
F2 = Actual Margin

If the user puts a value in A2, the formula returns a value in A4 but also in A5 & A6.
If the user puts a value in A3, the formula returns a value in A4 but also in A5 & A6

Originally, I wanted to write "if there's a value in both A2 & A3, return "Error" in A4 but got stuck with the "and" part. Here's what I have.

=IF(B2>1,(A2*(100+B2)%),IF(C2>1,(D2-A2)/A2)), IF(B2>1,and C2>1,"Error")

The idea here was to enable the user to play with the markup, margin or sell price and see how that affects the other two. Is there a way to lock the formula in D2 but allow the user to manually enter a value?

Thank you

 

[Somendra Misra]

Hi Hank,

In the absence of a sample file all I can suggest what is wrong in your formula.

=IF(B2>1,(A2*(100+B2)%),IF(C2>1,(D2-A2)/A2)), IF(B2>1,and C2>1,"Error")

The red portion is wrong: May be something like this

=IF(B2>1,(A2*(100+B2)%),IF(C2>1,(D2-A2)/A2, IF(AND(B2>1,C2>1),"Error")))

May be it's a typo while typing the thread. If so than please upload a sample file and ignore above comment.

Regards,

 

[Hank Moore]

Somendra,

Yes, IF(AND(B2>1,C2>1),"Error") works. Thank you. However, the formula returns two values now and I'm not sure how to remedy. I tried moving the last part to the front but same result. And don't forget, D2 needs to have an embedded formula (that is locked) but will also accept a manual entry. Make sense? I've uploaded a sample file

 

[Nebu]

Hi Hank:

Your files seems to be corrupted.

Thanks
Nebu

 

[Somendra Misra]

@Hank Moore

This is the formula you are using below are some observation :

= IF(AND(B2>1,C2>1),"Error",IF(B2>1,(A2*(100+B2)%),IF(C2>1,(D2-A2)/A2)))

B2 is % so you must enter a value less than 1 say 0.1 and format it as %age, currently you are using 10.

Now If desired margin is also in %age, than above also goes for it.

Now you are using this formula in cell D2 and in the formula you are ref. to D2 (See blue part in formula above), this will generate circular ref. error.

So my suggestion is to re-think on your calculations and logic.

Regards,

 

[Hank Moore]

I believe I've fixed the calculation issue(s) but truth be told, I also think I've just walked deeper into the quicksand.... With regards to the logic, please put me out of my misery - am I even on the right track? Is there any way to have one "sell price" cell if I'm allowing it and the "desired markup" and "desired margin" cells for manual inputs?

 

[NARAYANK991]

Hi Hank ,

The attached file was developed in response to a similar question some time back. See if you can make use of the same technique.

Narayan