Forum Discussion
Matching in the same column on excel to multiply two rows of data together for each property
The relatively easy part is the last, for the formula to automatically pick up new properties.
One would have to make a few assumptions though about what you're doing here. The table is not exactly a standard table in that (for example) the numbers in Yr1, Yr2 and Yr3 sometimes count people (FTE being, presumably, Full Time Employees, although that's another thing you've left to your readers to assume); sometimes the numbers count square meters, sometimes Energy Cost or Security Cost per FTE. And so forth. That kind of inconsistency can wreak havoc with formulas, where consistency is expected.
My point is to ask a few questions:
- First, could you give an example of the calculation you're asking for--it probably is in fact fairly straight forward, once terms are clearly defined
- Second, it would appear that you are ONLY asking for the calculation of the line "Cost security per FTE", yet it's not clear what the relationship is between that line and the "Number of FTE" line. Does one influence the other? If so, how?
- Third, What's the effect if it's "Freehold" instead of "Leasehold"?
- Finally, please post a copy of the actual spreadsheet from which you took the picture; that way you help us help you even further by not expecting us to re-create it
The relatively easy part is the last, for the formula to automatically pick up new properties.
One would have to make a few assumptions though about what you're doing here. The table is not exactly a standard table in that (for example) the numbers in Yr1, Yr2 and Yr3 sometimes count people (FTE being, presumably, Full Time Employees, although that's another thing you've left to your readers to assume); sometimes the numbers count square meters, sometimes Energy Cost or Security Cost per FTE. And so forth. That kind of inconsistency can wreak havoc with formulas, where consistency is expected.
My point is to ask a few questions:
- First, could you give an example of the calculation you're asking for--it probably is in fact fairly straight forward, once terms are clearly defined
- Second, it would appear that you are ONLY asking for the calculation of the line "Cost security per FTE", yet it's not clear what the relationship is between that line and the "Number of FTE" line. Does one influence the other? If so, how?
- Third, What's the effect if it's "Freehold" instead of "Leasehold"?
- Finally, please post a copy of the actual spreadsheet from which you took the picture; that way you help us help you even further by not expecting us to re-create it
- Hi John,
Yes this is not a standard table and you are correct in stating FTE is Full Time Employees.
Of the property table I would like to create a formula that will summarise the security cost per year of all leasehold properties and then security cost per year of all freehold properties once I have this formula I will then do the same for Energy cost per year (Product of 'Number of FTE' and 'Energy cost per FTE (£/yr)
I have forwarded you a copy of the spreadsheet.
Thanks for your help- I just sent you a new and improved version of the spreadsheet, so I hope you come back and see this message.
