Hi guys, firstly could I give a big vote of thanks to all the generous people on this board who have been so helpful to me. Having spent the last two years barely scratching the surface of what Excel can do, it's so great to be now using formulae and other exciting functions with your advice and guidance.
I have 13 months worth of figures in colums from E to Q. Sept 06 is in E and Sept 05 is Q. I'm trying to acheive two things:
1) Indicate whether the agents spends in each row are up or down. I've used this formula:=IF(Q5>E5,"down","up") but I am ending up with "up" in rows where there is no data in E and/or Q. I'd really like the cells to be left blank unless there actually is data to be up or down. Is it possible to alter the formula to do this?
and
2) A percentage change between E and Q. I'm currently using this formula: =(E5-Q5)/ABS(E5) but
a) the div0 formula is very annoying - how can I copy this formula all the way down a column but have it ignore empty cells?
and
b) How can I automatically reverse the formula to work the other way around when Q is greater than E?
Thanks in advance for help with this s.sheet ...
Jo
I have 13 months worth of figures in colums from E to Q. Sept 06 is in E and Sept 05 is Q. I'm trying to acheive two things:
1) Indicate whether the agents spends in each row are up or down. I've used this formula:=IF(Q5>E5,"down","up") but I am ending up with "up" in rows where there is no data in E and/or Q. I'd really like the cells to be left blank unless there actually is data to be up or down. Is it possible to alter the formula to do this?
and
2) A percentage change between E and Q. I'm currently using this formula: =(E5-Q5)/ABS(E5) but
a) the div0 formula is very annoying - how can I copy this formula all the way down a column but have it ignore empty cells?
and
b) How can I automatically reverse the formula to work the other way around when Q is greater than E?
Thanks in advance for help with this s.sheet ...
Jo