## Doing a calc on a row of figures, where first and last cell varies

Hi.
A simple one I hope.

I have a row of cells, most of which contain a meter reading.
I want to have a cell calculating the 'total', which is the last cell value (of the row) minus the first cell value.
The problem is the row represents a months readings, which are entered on a
Mon-Fri basis.
So the first and last cells will vary from month to month.
Also there will be blank cells here and there - for the weekends.
Is there a way to tell Excel to simply take the value in the last cell in the row, wherever it is, and subtract from it the value in the first cell, wherever that is?
Thanks.

## Solutions to the Problem Doing a calc on a row of figures, where first and last cell varies

I hope I'm not prompting too quickly but has anyone any idea what I'm doing wrong with this?
Thanks.

Hi,

I just tested the formula you created and  and for me it works fine.
You didn't say what error you get.
Lets break the formula down into 2 parts:-

This bit which isn't an ARRAY finds the last number in row 11

=LOOKUP(2,1/(11:11<>""),11:11)

This bit which is an ARRAY so CSE finds the first value

=INDEX(11:11,MATCH(TRUE,11:11>0,0))

So with these 2 you should now have 2 numbers; the first and last in row 11 and you can subtract them.
What do you get?

Now if either of those formula finds a text value then of course it won't work and my
guess is it may be the ARRAY formula that finds a text value because you have some text at the start of the row.
To overcome that we can use this ARRAY formula (CSE) which finds the first NUMERIC value in row 11

=INDEX(11:11,MATCH(TRUE,ISNUMBER(11:11),0))

and if we put them together we get the ARRAY formula

=LOOKUP(2,1/(11:11<>""),11:11)-INDEX(11:11,MATCH(TRUE,ISNUMBER(11:11),0))

Run CHKDSK
• Go to the Start button.
• In the Search box, type "cmd" (without quotes) and press Enter.
• At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.

## Recommended Method to Repair the Problem: Doing a calc on a row of figures, where first and last cell varies:

### How to Fix Doing a calc on a row of figures, where first and last cell varies with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your computer.  Open it, and it will perform a scan for your computer. The errors will be shown in the scan result.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. When the Fixing part is done, your computer has been speeded up and the errors have been fixed