Forum Discussion
Replace option in excel
I want to find a single cell data and replace with values in three cells Eg : Finding 4 Replacing with 0.1 in one cell, 0.2 in another, 0.3 in another. Could anyone help with this please Thank...
Maybe you could give a bit more information about what you are trying to do and how the data is set up (it would be great if you attached a sample sheet without personal info). But I will guess at the situation. So if you have a column of data and every time you have a "4" you want to "replace" it with a value counting in tenths. But first of all, in excel you don't "replace" data but "use" data to create a new set of resulting data so lets say you have the following data in Col A and then in Col B you have the same data but with each "4" replaced with a count. If you must actually "replace" data you need to use a macro or you can use Find and Replace functionalities (but I will skip that for now). So to get the following:
Col A Col B
2 2
4 0.1
8 8
4 0.2
4 0.3
1 1
2 2
4 0.4
You can use the following formula in B1 and fill down
=IF(A1=4, 0.1*COUNTIF($A$1:$A1,4), A1)
Col A Col B
2 2
4 0.1
8 8
4 0.2
4 0.3
1 1
2 2
4 0.4
You can use the following formula in B1 and fill down
=IF(A1=4, 0.1*COUNTIF($A$1:$A1,4), A1)