# Using Formulas, but calculating on hard number?

#### euclid1179

##### New Member
I'm sure this is an easy fix but I just don't know how to go about doing it.

Here are columns I have:

New effective Price (K)
Presumed New Blend Price (S) - this works off of formulas in a separate book
Blended Price (X)

So, in column X I am taking the number from column S (which is calculated and not a "hard" number) and am adding .02 via the following formula: =+S6+\$S\$2 (the \$S\$2 holds the .02 number)

In the next column (Y) I am using the IF function to determine if column X=K.

I'm running into problem using this statement because it seems that excel is recognizing the formula only in column X and not the resulting number. So while Column X truly equals Column K, excel is telling me they do not equal because in column K it sees ".50" and in column X it sees "=+S6+\$S\$2"

Anyone know how to make column X look like a single number to excel?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### euclid1179

##### New Member
Update: New Problem.

I've updated the values via the copy + paste method, but now the number is returned with about 8 decimal places and I can't get it to return only 2. Have gone into cell format and that doesn't do it. Can anyone help Is the round function useful in this instance?

Sorry for the multiple posts, I've been reading other pages on this forum and found out how to do part one!

#### NBVC

##### Well-known Member
Try insert the Value() function: So then, Value(X1)

#### jimbojones

##### Well-known Member
Hi,

Sorry I don't understand what you are trying to do. In your original post you add a number to a value then compare to the previous value to check those that are the same....surely they will never be the same!?

Can you post a sample via HTML posting program?

That may help see the problem.

Sorry for lack of assistance.

James

#### euclid1179

##### New Member
James,

Appreciate your attempt to help! I realize my description is a bit vague but I don't have access to be able to show you what I'm doing. Let me lay it out in plain English.

In column K I have a new effective price sent to me by a supplier. In Column S I'm calculating what I believe the new effective price should be based on the price of other raw materials. In column X I'm taking the value of column S and am adding .02 (this is a fee for blending of the raws) and this will return a number that is either spot on, or very close to the new effective price column. So that everything jumps out at me, in yet another column I am trying to determine whether column X is not equal to column K by returning a "1." When I go to do this, however, the IF formula doesn't work as it is seeing the formula in X instead of an actual number. I've solved this problem by updating the value of the cell by using the copy/paste buttons, but now excel is seeing a number with many decimal places (I only want 2 decimals to show.)

SO - when I am looking at the spreadsheet, column X shows "0.68" but when I click on the cell it shows "0.6777892" How do I force excel to round to only two decimals?

I appreciate your help immensely!

#### NBVC

##### Well-known Member
=Round(argument,2)

#### jimbojones

##### Well-known Member
OK. Little clearer now I think.

First, the IF function will look at the result of the formula and not the text of the formula so there shouldn't be a problem there unless you have the formula cell formated as text.

Secondly it seems that before you do your comparison you need to apply the =Round() function to one (maybe both) column X and K (the two columns you are comparing)

So Formula becomes =IF(Round(X1,2)=Round(K1,2),1,0)

Replace 1 and 0 with whatever you need.

Any use now?

James

#### euclid1179

##### New Member
NBVC said:
=Round(argument,2)

This is what I've been trying, but it returns "1" instead of ".68"

Odd?

#### euclid1179

##### New Member
jimbojones said:
OK. Little clearer now I think.

First, the IF function will look at the result of the formula and not the text of the formula so there shouldn't be a problem there unless you have the formula cell formated as text.

Secondly it seems that before you do your comparison you need to apply the =Round() function to one (maybe both) column X and K (the two columns you are comparing)

So Formula becomes =IF(Round(X1,2)=Round(K1,2),1,0)

Replace 1 and 0 with whatever you need.

Any use now?

James

James,

If you are ever on this side of the pond, it will be my humble duty to innundate you with several rounds at a local pub. Your formula worked brilliantly!

Thanks again,
Steve

#### jimbojones

##### Well-known Member
Hahaha,

Glad I could be of assistance. And glad we got there in the end.

I'll hold you to the offer of beers if ever I'm over!!!

Cheers for now.

James

Replies
3
Views
186
Replies
1
Views
92
Replies
3
Views
151
Replies
3
Views
89
Replies
2
Views
92

1,195,905
Messages
6,012,229
Members
441,683
Latest member
XLGeezer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back