I need a formula that will calculate the following
Example
My price is £1000 and my competitor's price is £1200. So I am £200 cheaper. I need to show this as a percentage. If my competitor is cheaper than me then this needs to be shown as a percentage but in red and in brackets.
Hope I have explained myself effectively.
Thanks
sinternational,
Attached is an example workbook based on the criteria you described.
Column A is "My Price"
Column B is "Competitor Price"
Column C is "Cheaper by %"
In cell C2 and copied down is this formula:
=1-MIN(A2:B2)/MAX(A2:B2)
Then, cells C2:C21 have this conditional format formula applied to them:
=$B2<$A2
The format it uses is to color the text red and apply this custom format so that the percentage is displayed in brackets:
"["0.00%\]
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks for the reply.
Just a couple of things:
Part of the formula is (A2:B2) - can I change this to (A2+B2) and will it still work to calculate the difference as I have more than one competitor that I have to compare with.
I cannot seem to get the formula correct to show red when it is a negative figure.
Thanks for your patience.
Instead of a colon, you would use a comma to separate the necessary cells. So, for example, if you needed to compare A2, B2, D2, and F2, it would be:
=1-MIN(A2,B2,D2,F2)/MIN(MAX(A2,B2,D2,F2),A2)
As for the formatting to red, assuming the "My Price" is in column A, and using the competitor's prices in columns B, D, and F, you would use this for the conditional format formula:
=MIN($B2,$D2,$F2)<$A2
Last edited by tigeravatar; 02-02-2012 at 01:59 PM.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks