Comparing columns of figures by % and with text too

MsCynic

Board Regular
Joined
May 21, 2006
Messages
122
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
L

Legacy 68403

Guest
I can't help you with your first question but for the second question you could try something like

=If(E5="","",E5-Q5/ABS(E5))

This would leave the cell blank if the cell in E5 has no value.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Jo, try these --
Question 1:
=IF(OR(ISBLANK(E5),ISBLANK(Q5)),"",IF(E5>Q5,"Down","Up"))

Question 2:
=IF(OR(ISBLANK(E5),ISBLANK(Q5)),"",IF(E5>Q5,(E5-Q5)/E5,(Q5-E5)/Q5))

Denis
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top