...and if you still want to continue further with the IF format, I'd use cell references rather than the text for "Furniture", etc, so that you can change the names of the options and the formulae will still work. Remember you'll need to use $ in the references to make them work properly when you copy and paste the formula into all the rows (apologies if you already knew that).
Additionally, if you are aiming to make it client-proof, you'll need to look at cell validation, so that a classification not in your list can't be entered. If you do, though, OpenOffice has a bug where if you specify a validation as a range of cells, and then extend the range, the validation formulae are not updated. I don't know if Excel does the same, but you can easily try it out to see. The workaround in OO is to create a named range for the validation values, and then use that in the validation formulae, saving some hassle when you add a new classification in six months time.
I'd also think about whether fractions of a pence are important, for column K. There are various rounding functions available, depending on how you want to handle fractions of a pence. Remember that what is displayed is not necessarily what is stored, but if you are using this spreadsheet to manually copy figures across, and you are not cumulating the column K results over time, then you're probably ok as you are.
-- Edited by Rob-f58049 on Wednesday 15th of August 2012 03:15:27 PM
I've been working on the same formula for over an hour now and it bugging the hell out of me!! Let me set the scene:
I'm trying to create an automated fixed assets register, one thats fool proof for clients really, and makes my life easier. So i thought id experiment on myself, after all isn't that the way the greats did it!! lol
Column 'F' is the classification, which uses a drop down list to choose from, they are Vehicle, Furniture, IT equipment, and Miscellaneous
Column 'J' is the rate of depreciation (not sure this is needed and the formulas could go in column 'K', so you don't see the level)
Column 'K' is =sum(G4(the cost column)*J4)
What im trying to do and failing miserably at is get 'J' to display the rate of dep'n for the values displayed in 'F'. What i have so far is: =IF(F4="Furniture"),20%,IF(F4="Vehicles"), 25%,if(F4= "IT Equipment",33%,if(F4="Miscellaneous",25%,If(F4="","",""))). I can get it to work for one variable, but as soon as I put any others In it doesn't work. I was thinking do i even need to display the depr'n in column 'J' and can it not just be worked into the formula in 'K'?
I'm sure as soon as someone posts the right answer i'll see how much of a div i've been and it'll be obvious where i've gone wrong, but at the moment the 'red mist' is descending and I cant see the wood for the trees! Is there anyone out there who could offer advice and maybe some help?? PRETTY PLEASE! lol
the formulae as you have it will only work for the first variable.
The issue is that you are using the wrong function for the job.
Rather than an IF statement you need a Vlookup table with each possible value from F4 in a handy little table that will return the correct rate for the value entered.
I'll let you go and have a play with the Vlookup function then come back with specific questions later,
all the best,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
I think i will have to look into the Vlookup as the NBV and Accum'd Dep'n are showing #VALUE, clearly showing that theres something wrong with the formula. As you say Vlookup might remedy these issues; as cost less accum'd dep'n is showing nothing because theres no figure in the accum'd b/f column.
