UKC

I've been using Excel to record my training; I have a spreadsheet that I can enter; the date, venue, grade, and rest interval for up to 10 routes. I now want to add a column for each route length so I can calculate the total distance climbed. However when you climb a 10m climbing wall you don't climb 10m. You start with your centre of gravity (just below your navel) at approx 1m from the ground, and you finish with it approx 1m from the top. To actually climb 10m on a 10m wall you would have to finish stood on top of the wall. So to take this into account I have been using the formula int=(a1-2)+(b1-2)+etc... But if I only do 8 routes out of the 10 I've allowed for, the -2s in the empty cells affect the result.
How do I get Excel to ignore these empty cells?
Ta.

In reply to Serpico:

do another column with an IF logic line. If cell A1 is <0, let B1 be 0. If cell A1 is >0 then let B1=A1.

In reply to Serpico:

use an If statement somewhere


e.g. formula in B1 could be =IF(A1="",0,A1-2) where A1 is the cell that contains the route length will produce result in B1 of route length. Drag formula down for B2,B3 etc.
Will ignore calculation if nothing entered in the column A

In reply to Serpico:

Use an IF statement to do the calc only if the ISBLANK() for the cell is false. I'm sure there's a way with COUNTIF and SUMIF too if I had a little more time to do it.

In reply to ebygomm:
Thanks. Copied and pasted your formula, changed the cell nos and it works.

In reply to Serpico:

Well for each route you climb 2m less than the length of the route so by the end of the evening you will have done 2*(no.routes) less than the sum of the route lengths. So you can put at the end of the row:

=SUM(A1:H1)-2*COUNT(A1:H1)

and this will do what you want - provided you leave a blank rather than a zero where you haven't done a route. Seems the simplest way that to me.