# Excel - IF Function Problem - Expert Solution

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
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
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
Excelchat Expert 10/09/2018 - 02:19
ok
Excelchat Expert 10/09/2018 - 02:22
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

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.