Novice Help Please

I'm trying to work out a formula to give me a max bid price along with a percentage. Let me try to explain. Column A = Best Web Price. Column B = Bid Price, Column C = 20% of Column A, Column D =17.5% of Column A, Column E = 20% of Column D, Column F = Total of A,C,D & E. So far I've got all the above working fine. I want to work out a margin of 38% of Column A once all the other columns are added up and subtracted from Column A and Column G to give me a Max Bid price to enter into Column B to maintain 38%.

Hope I've explained that OK.

I've attached an example.

Appreciate any help

Hello
It looks like you have added some columns to the attachment so that it doesn't tie up with the narrative in your post (eg Col B is not Bid Price). Please can you review to correct what you wish to do with each column.

Your columns in the example were off from your description above, but if I interpret correctly what you are looking for, see the attached worksheet which changes the formulae in columns D, H, and I.

To explain my methodology, in case you need to amend anything, I will tell you how I arrived at the formulae:

Column H, the Total Bid, is 62% of the Price in Column A, leaving 38% as your margin.

Since your Total Bid includes add=ons totaling 141% of the bid price, Column D is arrived at by dividing Column H by 1.41.

The corrected formula in Column I then becomes =(A5-H5)/A5 and copied down.

I hope I have interpreted your request correctly!

I think Ive managed to work out something by making a few adjustments to your worksheet. I calculated the highest bid prices (Col J), but ive amended your margin formula (Col I) as I believe this should be a percentage of Col A (the price you will sell at). That said, if you enter the highest bid prices in Col D, it reports a 38% margin in Col I.

I have given more thought to your Bid Spreadsheet, and I've come up with changes that I believe will enhance your usability.

It finally registered with me that you were looking for the Maximum Bid as a guideline for your Actual Bid in order not to overbid an item. I placed that column to the left of the column for your Actual Bid and corrected the remaining calculations according to your original design.

I also made your sheet a Structured Table so that new entries below existing rows will automatically propagate the formulae you have in place.

I hope you find these changes worthwhile.

Bid Spreadsheet

skydivermel said:

I'm trying to work out a formula to give me a max bid price along with a percentage. Let me try to explain. Column A = Best Web Price. Column B = Bid Price, Column C = 20% of Column A, Column D =17.5% of Column A, Column E = 20% of Column D, Column F = Total of A,C,D & E. So far I've got all the above working fine. I want to work out a margin of 38% of Column A once all the other columns are added up and subtracted from Column A and Column G to give me a Max Bid price to enter into Column B to maintain 38%.

Hope I've explained that OK.

I've attached an example.

Appreciate any help

Click to expand...

A big thank you to all whom took the time out to work on this and assist me.

Thanks again, I'm a very happy man.

Mel

skydivermel said:

Thanks again, I'm a very happy man.

Click to expand...

Glad to be of assistance and thanks for the feedback!

Likewise from me!