Change cell values according to a percentage in an absolute cell

**jaypmike** · 06-07-2011, 01:12 PM

I have a spreadsheet containing prices for printing a brochure. I want to be able to change the price in each cell depending on the percentage in an absolute cell. For example, the absolute cell is B1 (the percentage in the cell is 100%) and the price in cell C4 is $395. If I change the value of the percentage in absolute cell B1 to say 50% then I want cell C4 to be automatically updated to $197.50, 25% to $98.75 and so on and so forth.The prices differ across the cell depending on size, fold, and quantity. I know there is a quicker way to accomplish this rather than tediously typing the price * $B$1 across all the cells. If all the cells contained the same price then autofill would be a feasible option but the prices all differ. Any help is much appreciated. Thank You.

**Richard Buttrey** · 06-07-2011, 01:28 PM

Hi,

I don't understand your question. You seem to want C4 to hold both a value and a formula which uses B1. Without a macro, (and I suspect you're not wanting this) a cell can only hold either a constant or a formula.

Maybe what you want is the constant $395 in C4 and then a formula in C5 which is
=C4*$B$1

If you have other values in C4:Z4 then drag C5 across to Z5.

Regards

**jaypmike** · 06-08-2011, 03:15 PM

Richard,

Thank you for your prompt reply. I have uploaded a spreadsheet to illustrate what I am talking about. It contains prices for printing a brochure depending on size, folding and quantity. Cell B1 is the absolute cell containing a percentage; when I change the percentage to say 75% or 50%, I want all the prices in the spreadsheet to be updated so they are 75% or 50% of their original price. For example, cells C3:J3 contain the prices $395.00, $488.70, $504.24, $650.08, $841.00, $1,470.00, $2,149.00, $2,583.25, $3,219.36 and that is when cell B1 is at 100%. When I change the percentage in cell B1 to 50% I want it to automatically update all the cells to 50% of their original value, so cells C4:K4 would be $197.50, $244.36, $252.12, $325.04, $420.50, $735.00, $1,074.50, $1,291.63, $1,609.68. So far the only way I've figured out how to do this is to go through each cell and type (price) * $B$1 which I have done for cells C3:J3 but to do this to each cell is very time consuming and tedious as I have over 10 other spreadsheets I am trying to do this to. I was wondering if there was any way to apply *$B$1 to all the cells containing prices. You mentioned a macro, how might I do this with a macro? Thank you again for any help or advice you can offer up.

Regards,
Jason Mikesell

P.S. - The decimal point should be moved 2 places to the left for all the prices in the spreadsheet.

**MrShorty** · 06-08-2011, 04:27 PM

I'm sure it could be done with a macro, but it would likely be a lot more complicated than needed. the macro would need a loop to go through each cell, look at the formula in each cell to extract the "base rate", then change the formula to the new value, and you'd probably want it all associated with the worksheet_change event so that it would happen automatically.

The easiest solution as I see it, though is just like Richard suggested -- a simple cell formula. To preserve the printable format of your spreadsheet, I'd probably copy all of the "constant base rates" that are currently in your spreadsheet to another section (for illustration, copy over to columns AA-JJ). Then a formula in the current "printable" section =AA3*$B$2. Then hide columns AA-JJ so they don't print.