• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace Cell Reference by Value without formula result

J

JohnMSmith_123

New Member
Hi,

I have several formulas that I want to convert to values without the actual result.

For instance:
Cell A1 = 5
Cell A2 = 4

Cell A3 = =(A1 + A2) / A2
Cell A4 = (5 + 4 ) 4

How can I do the A4?
 
Hi ,

If you can explain your real objective , it will be possible to suggest an appropriate solution.

Assuming that what you have posted is possible , and is done , what will happen when the content of A1 or A2 changes ? A3 will change because Excel does that automatically , but what about A4 ? Should that also change ? And if yes , then what is the purpose of having A4 , when it does exactly what A3 does , but in a different way ?

If A4 is not supposed to change , then what is the purpose ?

Secondly , you have given a simple example of addition ; will the formula always be the same ? What if the formula is a more complicated one ?

Narayan
 
NARAYANK991 said:
Hi ,

If you can explain your real objective , it will be possible to suggest an appropriate solution.

Assuming that what you have posted is possible , and is done , what will happen when the content of A1 or A2 changes ? A3 will change because Excel does that automatically , but what about A4 ? Should that also change ? And if yes , then what is the purpose of having A4 , when it does exactly what A3 does , but in a different way ?

If A4 is not supposed to change , then what is the purpose ?

Secondly , you have given a simple example of addition ; will the formula always be the same ? What if the formula is a more complicated one ?

Narayan
Click to expand...


My formulas will be always different, not a simple addition.

This is for my assignments: I have to create several formulas, and the professor whats to see the numbers plugged in into the formulas. so, not only i have to create the formulas, but I need to show what are the numbers that were part of it.
 
If it is for display purpose, Value function can be used.
="("& VALUE(A1) &"+"&VALUE(A2)&")/"&VALUE(A2)
This will display (5+4)/4 on the screen, and can still update itself, if the values in A1 or A2 change. Likewise, append further with "&"="& value (a5) to give results also, to display (5+4)/4=2.25

But like Narayan said, what is the real objective ? Please share.
 
Sudhir Prabhu said:
If it is for display purpose, Value function can be used.
="("& VALUE(A1) &"+"&VALUE(A2)&")/"&VALUE(A2)
This will display (5+4)/4 on the screen, and can still update itself, if the values in A1 or A2 change. Likewise, append further with "&"="& value (a5) to give results also, to display (5+4)/4=2.25

But like Narayan said, what is the real objective ? Please share.
Click to expand...

Yes, the objective are right, the problem that I have to do it for more than 100 formulas. which means, that it would be easier just to write the actual number instead of changing each formula to add "s, VALUE's, etc...
 
Writing actual numbers could lead to errors, in event of changes. If this is to be demonstrated to the professor, you could try the formula > Evaluate forumla option in Excel, which displays intermediate results.

Else you would need an elaborate VBA coding.
 
If your objective is just to show the formulas, you may try "Formulas > Show Formulas" option. It will display all the formulas instead of actual results & also links to cells to which they belong.
 
You must log in or register to reply here.
Back
Top