Question description:
This user has given permission to use the problem statement for this
blog.
I have a list of cost prices in one column and i would like to know what formula to use to add a percentage value. E.g if my cost price is just £1 i would like to create the sell price to be perhaps £3, but if my cost price is £1000, i would like the sell price to perhaps be £1,100 (not £3,000).
Solved by M. Y. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/09/2018 - 01:33
Hi…Welcome to Got It Pro
Excelchat Expert
10/09/2018 - 01:33
Could you please share some sample data on the preview sheet?
User
10/09/2018 - 01:35
Can you see what i am doing?
Excelchat Expert
10/09/2018 - 01:36
Yep...thanks for the sample data.
Excelchat Expert
10/09/2018 - 01:36
So you would like to add a certain determined percentage to cost price to arrive at a sell price, right?
Excelchat Expert
10/09/2018 - 01:36
Is the percentage going to uniform for all products or different for each product?
User
10/09/2018 - 01:38
The percentage will depend on the cost price
User
10/09/2018 - 01:38
The cheaper the product, the higher the margin
Excelchat Expert
10/09/2018 - 01:38
Ok...so not on the product
Excelchat Expert
10/09/2018 - 01:38
Do you have certain range of cost and associated percentage?
User
10/09/2018 - 01:39
I need Excel to automaticaly calculate the margin increase depending on the value within the set range
Excelchat Expert
10/09/2018 - 01:39
Yep...I am making a matrix in column L, M and N.
Excelchat Expert
10/09/2018 - 01:40
Could you please fill in some dummy range?
User
10/09/2018 - 01:40
Some products that are very low value i.e. less than £1 need to be increased by 700%
User
10/09/2018 - 01:42
Are you waiting for me?
Excelchat Expert
10/09/2018 - 01:43
I have created a formula in column C and column D.
Excelchat Expert
10/09/2018 - 01:43
Cell C2 formula will look at the matrix on the right to fetch the appropriate percentage based on the Cost.
Excelchat Expert
10/09/2018 - 01:43
I am using IF AND formula to do this.
Excelchat Expert
10/09/2018 - 01:44
Please review and let me know if you need me to explain the formula.
User
10/09/2018 - 01:46
Okay, is it not possible for Excel to automatically calculate the sell price if it works on a range?
Excelchat Expert
10/09/2018 - 01:47
You will have to supply the range that you need the % for, otherwise Excel will not know which percentage to consider.
User
10/09/2018 - 01:48
Okay
Excelchat Expert
10/09/2018 - 01:48
But if you do not want the matrix on the right, the values can be hardcoded in the formula.
User
10/09/2018 - 01:48
If i was to create the matrix on one sheet, how do i connect it to the main sheet?
Excelchat Expert
10/09/2018 - 01:49
But if you have several ranges that it is more meaningful and easier to handle with a matrix, where you can amend the percentage without having to change in the formula directly.
Excelchat Expert
10/09/2018 - 01:49
Let me show that
Excelchat Expert
10/09/2018 - 01:51
Now the Matrix in another sheet
Excelchat Expert
10/09/2018 - 01:52
The formula is the same, just cell reference is to the other sheet to get the percentage.
Excelchat Expert
10/09/2018 - 01:52
Both the sheets are now connected
User
10/09/2018 - 01:53
Sorry, i'm still a little unclear
User
10/09/2018 - 01:54
Which column do i need to edit in the main sheet?
Excelchat Expert
10/09/2018 - 01:54
Could you tell me which part you were unable to follow?
Excelchat Expert
10/09/2018 - 01:54
Price Matrix sheet is all manual entry
User
10/09/2018 - 01:54
I'm trying to replicate what we are doing on another spreadsheet
Excelchat Expert
10/09/2018 - 01:55
In Main sheet, I have highlighted the column which has formula in Yellow.
User
10/09/2018 - 01:55
Presumably i select C2 and enter a formula?
Excelchat Expert
10/09/2018 - 01:55
That's correct.
User
10/09/2018 - 01:55
And what formula do i need to enter?
Excelchat Expert
10/09/2018 - 01:55
C2 has the formula which is link to the Price Matrix sheet.
Excelchat Expert
10/09/2018 - 01:56
=if(and(B2>'Price Matrix'!$A$2,B2<='Price Matrix'!$B$2),'Price Matrix'!$C$2,if(and(B2>'Price Matrix'!$A$3,B2<='Price Matrix'!$B$3),'Price Matrix'!$C$3,if(and(B2>'Price Matrix'!$A$4,B2<='Price Matrix'!$B$4),'Price Matrix'!$C$4,"")))
Excelchat Expert
10/09/2018 - 01:57
The formula is checking the cost price in B2 whether it falls within one of the ranges prescribed in Price matrix through IF AND function, then returns the corresponding %.
Excelchat Expert
10/09/2018 - 01:57
Were you able to get that?
User
10/09/2018 - 01:59
Could i see if i could do it on this spreadsheet?
Excelchat Expert
10/09/2018 - 01:59
Sure...please go ahead and I can help you if you get stuck.
User
10/09/2018 - 02:01
This is where i get stuck ! :)
Excelchat Expert
10/09/2018 - 02:02
You are doing fine.
User
10/09/2018 - 02:02
So i type =IF
Excelchat Expert
10/09/2018 - 02:03
Yes, type =IF(AND(
User
10/09/2018 - 02:03
okay
Excelchat Expert
10/09/2018 - 02:03
then select cost in the corresponding row
Excelchat Expert
10/09/2018 - 02:04
then type >
User
10/09/2018 - 02:04
okay
Excelchat Expert
10/09/2018 - 02:04
then go to Test Matrix and select cell A2 which is the first cost in the matrix
Excelchat Expert
10/09/2018 - 02:05
There is catch here. You will see the that I have $ sign before every cell reference from the Matrix sheet.
User
10/09/2018 - 02:05
oh yes
Excelchat Expert
10/09/2018 - 02:06
This dollar symbol is the lock the cell and its called absolute reference so that when you drag the formula down, the cell reference from matrix remains lock and always references the desired cell.
Excelchat Expert
10/09/2018 - 02:06
To get this dollar symbol, you can press function key F4 on your keyboard.
Excelchat Expert
10/09/2018 - 02:07
Then continue the process to get the other criterias of the formula.
Excelchat Expert
10/09/2018 - 02:08
Is it the first time you are trying to enter a formula in excel?
User
10/09/2018 - 02:08
No!
User
10/09/2018 - 02:08
I only use basic fomulas usually
Excelchat Expert
10/09/2018 - 02:08
This a very basic IF formula.
Excelchat Expert
10/09/2018 - 02:09
You will just need to follow the logic of the formula and click on cell references for it to pick up.
User
10/09/2018 - 02:10
What to i select after i have selected cell A2?
Excelchat Expert
10/09/2018 - 02:10
Please look a the formula I had given you earlier and follow the logic.
Excelchat Expert
10/09/2018 - 02:11
We are putting an AND condition which means both conditions have to be satisfied. That is the value has to more than the Min and Less than or equal to Max
Excelchat Expert
10/09/2018 - 02:13
So you enter first AND condition for the MIN value and then 2nd condition for MAX value
Excelchat Expert
10/09/2018 - 02:15
IF statement syntax is = IF(AND(Cost price>0,Cost price<=0.99),90%,"")
Excelchat Expert
10/09/2018 - 02:15
This is the basis syntax.
Excelchat Expert
10/09/2018 - 02:15
We follow the same logic and do multiple nested IF statement to suffice all conditions in the Matrix.
Excelchat Expert
10/09/2018 - 02:18
Do you want me to do one of the formula for you?
User
10/09/2018 - 02:19
yes please
Excelchat Expert
10/09/2018 - 02:19
ok
Excelchat Expert
10/09/2018 - 02:22
Please look at D12
Excelchat Expert
10/09/2018 - 02:23
Now you can just drag this formula down and do not have rewrite it for all the rows.
User
10/09/2018 - 02:28
Okay, thanks for your help. I don't want to take up anymore of your time.
User
10/09/2018 - 02:28
Many thanks
Excelchat Expert
10/09/2018 - 02:29
Sure...Please do come back if you need any further help.
Excelchat Expert
10/09/2018 - 02:29
Thanks for your time
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
