2016년 9월 29일 목요일

How to Fix Problem - Doing a calc on a row of figures, where first and last cell varies?

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

Download SmartPCFixer to Fix It (Free)

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.
  • Restart your computer.

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


Related: Way to Update & Download SONY SVE14A2X1EH Wireless State Device driver,How to Update & Download SONY SVS1511X9RB Graphics Driver for D2 (Intel) v.9.17 - 9.17.10.2828,Method to Download SONY VGN-CR290N Realtek High Definition Audio Driver v.5.10.0.5433,Download SONY VGN-FS710/W Utilities DLL Update v.6.4.0.06290 driver,Method to Update & Download SONY VGN-NR460E/P Intel Graphics Media Accelerator Driver v.6.14.10.4847,How to Fix Error 0x0000c1f5 Solución?,Error 0x80071a91 Win7 Fix Patch,Error 0x800ccc60 Windows Live Mail [Solved],Error Message 0x800ccc79 Outlook 2000 Fix,What is Error 0xc1ab0001?,[Answered] x64 saplugin dll,How to Fix Problem - Kb943729 Download?,Troubleshooting: Bccode D1 Error,Java Runtime Tech Support,Troubleshooting: ERROR NO SUCH GROUP Error,Solution to ashoutxt.dll Missing Error,Solution to cxcore096.dll Missing Error,Where can I Find dsauth.dll File,How to Fix msde.dll Not Found Error,Best Method to Fix psizewizres.dll Error
Read More: How Can You Fix - Do you have help for first time office user??,How Can You Fix - Drag and Drop in Word (W7) does not work?,Don't like Windows 7 Paint, can I install the Windows XP version of Paint? [Solved],How to Fix Problem - do I need to update my windows 7 mediacenter cuz it makes my puter crash?,Downloaded IE10 for Windows 7 and now it will not load any webpage,Data validation question. Maintaining and updating named ranges.,Desktop Gadget Gallery on Windows 7,delete empty rows,Deleting a page and also how can I copy an exact page lay out on a new page,Date Formula for 1st Sunday of each Month (3rd to 9th)

댓글 없음:

댓글 쓰기