Compute formula within current year

[markmkramer]

I have a data sheet that will eventually contain figures from multiple years, but I want the equations to be computed based on current year figures. Is there a simple way to constrain these equations with an array lookup or other function? I don't want to modify my current equations, but rather just add the constraint.

If you have questions, please ask.

[Glenn Kennedy]

Without seeing anything at all, it's kinda difficult to provide any answer. So... Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[markmkramer]

Notice a few bugs while inputting additional data for 2016, but it shouldn't affect your equations. The equations that I want to constrain are in the blue box on Info page. If you can work with the Red equation, I think I can apply it to the rest. Data tab contains data.Gas Mileage - Test.xlsx

[markmkramer]

Forgot to highlight in red. Use this file.

[Glenn Kennedy]

OK. I hope this is correct. Please check it over manually!!! The modified formulae are in the pale green cells. These are Array Formulas. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[markmkramer]

I've used Array formulas before, but its been a while; roughly a year. This seems to do the job, thanks. I made a few mods to simplify the equation. If I can't figure out the rest of them, I'll get back to you later today.

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

Of course, if you have a problem - shout. I'm away now - there is life outside the EF!! Back in UK morning.

[markmkramer]

One last question. Are you willing to do the same thing for MPG, Miles per day, and Cost per day (highlighted in red)? Updated sheet. I'd very much appreciate it. After this I will consider this post solved. When I'm not tired, I'll study your formulas.

Thanks again.

[Glenn Kennedy]

Try this out. Test thoroughly... the cells in pale green can be deleted once this is sorted (they're just some intermediate steps, in case I need to unpick this!!).