Find percentage and change it

James Greenup

New Member
Joined
Jul 14, 2014
Messages
16
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Jul 14, 2014
Messages
16
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
Joined
Mar 27, 2006
Messages
1,175
Posting a small portion of your spreadsheet including expected results would help clarify.
 

James Greenup

New Member
Joined
Jul 14, 2014
Messages
16
I don't know how to add a file. Here only link is allowed as i can see
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
Try putting a border around the cells you wish to copy then copy and paste into this site
 

James Greenup

New Member
Joined
Jul 14, 2014
Messages
16
Try putting a border around the cells you wish to copy then copy and paste into this site
Namepricediscount averagediscounted pricecell pricecell discount
Jane156010%1,404.00=ROUNDUP(D2,-1)=(B2-E2)/B2

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

this will show

Namepricediscount averagediscounted pricecell pricecell discount
Jane156010%1,404.001,410.009.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
Joined
Mar 27, 2006
Messages
1,175
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
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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.
 

Forum statistics

Threads
1,089,207
Messages
5,406,848
Members
403,109
Latest member
gamer527

This Week's Hot Topics

Top