# Comparing columns of figures by % and with text too

#### MsCynic

##### Board Regular
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
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

#### MsCynic

##### Board Regular
Thanks! You guys are great.

Replies
0
Views
204
Replies
4
Views
183
Replies
6
Views
687
Replies
1
Views
566
Replies
3
Views
712

1,172,166
Messages
5,879,420
Members
433,427
Latest member
OutofOffice

### 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.

### Which adblocker are you using?

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

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