Hi everyone, I'd like to find a way so that if you enter a number in a cell of the column D, the rest of the column will fill automatically. I can't find a way to do this. Hope the image makes it clear to understand.
I wish it was that easy. What I want is that, wherever I input a value, it will fill the rest of the table for me. Any cells of the column D. That way if I know the price of a specific option, it will tell me what it would be if was using a different option. Hope I'm clear. Thanks for helping!
Pascal, I could probably create a User Defined Function (UDF) that would be a more glamorous solution but the formulas I've provided below will work if you can use Columns E to enter the value you are wanting to assess and F to enter Formulas that are used to give you the values you are wanting.
In cell D2 enter this formula: =IF(E2>0,E2*F2,IF(E3>0,E3*F3,IF(E4>0,E4*F4,IF(E5>0,E5*F5,IF(E6>0,E6*F6,IF(E7>0,E7*F7,IF(E8>0,E8*F8,IF(E9>0,E9*F9,IF(E10>0,E10*F10,IF(E11>0,E11*F11,IF(E12>0,E12*F12,IF(E13>0,E13*F13,0))))))))))))
In cell D3 enter this formula then copy it down to row 13: =(1+C3)*$D$2
In cell F2 enter this formula then copy it down to row 13: =1/(1+C2)
You can verify the accuracy of the Formulas and outcome by entering "100" in cell E2.
Now, enter any value in Column E on the row for the price you have to see what the prices would be for the other options.
For this solution to work properly you must only populate 1 value in Column E.
Let me know how this works for you. If you have more combinations that what you initially discussed then an UDF would need to be created.