divide a total, subtract one then format as a percentage

[Guest]

i need to create a formula that requires me to divide a total by the average
total, subtract 1 from the result before formatting as a percentage

 

[Guest]

=(AVERAGE(A1:A100)/SUM(A1:100))-1

 

[Guest]

The formula requires me to divide the store total by the average total and
then subtract one from the result, before formatting a percentage. I need to
use an absolute cell reference for the average total when copying formulas
for the store comparison figure. Will your answer do this?

 

[Roger Govier]

Hi Cath

With your store values in A1:A100, then in B1
=A1-AVERAGE($A$1:$A$100)-1
Format the cell as Percentage.
Copy B1 down through B2:B100
Change the range to suit the number of rows of store data.

--
Regards

Roger Govier


"(e-mail address removed)" <[email protected]>
wrote in message

 

[Guest]

maybe i am confused..
"copying formulas for the store comparison figure."

let's say your store values are on A1:A10 [a total of 10 store]

if i want to compare store values of a same product.

1. on B1=A1/sum($A$1:$A$10) [click %]
hence each store will have a % of the total store
summing it to a total store equal to 100%.
this is the way i guess for comparing each store.

2. comparing the store with the MEAN average of all store
on C1=A1/AVERAGE($A$1:$A$10) [click %]

happy holidays