Search code examples
excelexcel-2007worksheet-function

Excel Same cell as Input and output

I have an Excel sheet that has multiple columns with decimal values having two digits after the decimal e.g. 15.98, 14.42.

I wrote a formula that changes the value after the decimal point to 0.45 or 0.95, Formula is as follows

=IF(AND(MOD(M8,1)>=0,MOD(M8,1)<=0.49),0.45,0.95)+FLOOR(M8,1)
  1. I can apply this formula to a cell and display the output in a different cell, but I want the formula to be in the same cell as the input and output (which doesn't seem possible).
  2. As I apply the formula to the first column of a row, after clicking the cell handle it is applied to all the rows below (vertically), but how would I go about getting this formula to apply to all rows and columns at once?
Solution

  • You would have to use VBA to get your input to transform to your output in the same cell.

    You can copy the formula, then select the whole sheet (by clicking the upper-left hand corner box), then pasting, BUT this will only really work for formulas without references (like =1+1), anything with a reference will cause you to have a self-referencing cell. It's a very good method if you select rows and columns that are outside of the referenced cells range.