how to change cell reference for calc based on different cell manually entered value?

asked 2020-02-01 13:24:23 +0200

I have a spreadsheet with a number of columns

Columns A Column B etc....... Date Numbers

At the top of the spreadsheet I have a manually entered the range 1-10 (ManVar) In column A I have the date that that row of data applies to In column B I have a range of increasing number based on externally supplied data In column F I have a range of numbers based on externally supplied data

Columns D and H have calculations based upon a simple calculation to give me a percentage for the increase from the previous day

However, the data sets are currently uncertain whether the data in Column B and F 'actually' happened on that day or up to 10 days earlier,

i.e The simple model would be cell in (Column Fx)/(Column Bx)=PercentageAnswer there's potentially a 'delay' in the correct date actually matching the model should be

cell in (Column Fx)/(Column B(x-ManVar))=PercentageAnswer

This allows me to 'toggle' the ManVar variable at the top of the sheet to see what the change in the dates has on the output percentages.

I hope this explanation is fairly clear, so, how can I get the sheet to use ManVar to change which cells it uses for the calculation


edit retag flag offensive close merge delete


Please edit your question to attach a sample file, with notes about what you want achieve, to make for someone help you,

m.a.riosv gravatar imagem.a.riosv ( 2020-02-01 17:58:17 +0200 )edit