# Find percentage and change it

#### James Greenup

##### New Member
I have several customers. Everyone has her own discount %. When I write a name, I want to find their discount %,calculate the price, round the price up, then to change the discount number. For example if Jane has 10%, the price is 960,after discount it will be 864, but as i will take only with 100\$, i need to round it up,it will be 900, so it means that the clear % of discount war (960-900)/900=6,6%, and i want that 10% to be replaced by this 6,6%. And also don't want to get circular referencing error

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### West Man

##### Well-known Member
and i want that 10% to be replaced by this 6 said:
I'm unsure if this is what you really want because if Jane ordered the exact same thing(s) later, the discount then would be different. I think you want to compute an "applied discount" to be used for each order. This also eliminates the circular reference problem

#### James Greenup

##### New Member
and i want that 10% to be replaced by this 6 said:
I would like the % to round up as well, for example if she had bought 3 times, not to bring the percentage of one (may be 1 was 10 times more expensive), but total percentage of all previous trades rounded up(as price will be rounded up, the % is lower). After again I need what i wanted previously. To use this %,round number and change %. if i add 2 columns and hide them,i can(first will round up the % used price, second will show how many % is used and the begining % will be hidden).
I know that my explanation is very hard to understand,but anyway

#### West Man

##### Well-known Member
Posting a small portion of your spreadsheet including expected results would help clarify.

#### James Greenup

##### New Member
I don't know how to add a file. Here only link is allowed as i can see

#### West Man

##### Well-known Member
Try putting a border around the cells you wish to copy then copy and paste into this site

#### James Greenup

##### New Member
Try putting a border around the cells you wish to copy then copy and paste into this site
 Name price discount average discounted price cell price cell discount Jane 1560 10% 1,404.00 =ROUNDUP(D2,-1) =(B2-E2)/B2

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

this will show

 Name price discount average discounted price cell price cell discount Jane 1560 10% 1,404.00 1,410.00 9.6%

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

And somewhere is written Jane 10%,it means my 10% is like lookup formula. Now I want this table but without discount average and discounted price. I want only a,b,d,e column. Did i explain better

#### West Man

##### Well-known Member
Two simple ways to not show the columns C and F

1. Hide them
2. Assuming your data is in sheet1, on a separate sheet in A2 use =SHEET1!A2
in B2=SHEET1!B2
in C2=SHEET1!D2
i
n D2=SHEET1!E2

#### James Greenup

##### New Member
Thanks but both are like hiding, which i didn't want

#### West Man

##### Well-known Member
You can certainly get rid of column C by incorporating your vlookup in the calculations for column D. Column F is the problematic one as I understand you need the number for future use. If that is the case, the number must be stored somewhere.