Using Formulas, but calculating on hard number?

euclid1179

New Member
Joined
Sep 22, 2004
Messages
14
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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?

Disable AdBlock

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
Back
Top