|
General Random thoughts and ideas. "General" does not mean random drivel, nonsense or inane silliness. |
|
Thread Tools | Search this Thread | Display Modes |
#1
|
||||
|
||||
Need help with an MS Excel Script/Formula
I'm working on a form for my job (A monthly mileage statement). We record end of day mileage from the bottom up in column A, and the daily use in column B. 31 lines on the form. So Day 1 is Cell A41. (There are header cells)
What I'd like to do is have Cell B40 and up scan down to the next filled A cell and subtract out the Current day's end from the prior day's end to give the actual use. Essentially B40=A40-A41. However it has to be smart enough to skip a null cell (weekends, days out, etc). Do I need a script or can this be done in a straight formula in a cell? Thanks ~Dennys GENERATION 27: The first time you see this, copy it into your signature on any forum and add 1 to the generation. Social experiment |
#2
|
|||
|
|||
Re: Need help with an MS Excel Script/Formula
Quote:
|
#3
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
Quote:
GENERATION 27: The first time you see this, copy it into your signature on any forum and add 1 to the generation. Social experiment |
#4
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
pm me with your email address and I'll do it for you.
I was famous, once... http://www.heroscapers.com/community/blog.php?b=1715 Visit my site: http://www.superflycircus.com "I'm not cute...I'll mess you up!" ~Jake The Dog |
#5
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
Can you use column C (or any hidden column) to run a mileage tally (=sum(c40+b39))? Then all you need to do is subtract c40 from c39 to get your daily total.
~Aldin, who suspects there is a formula, but usually cheaps out like this He either fears his fate too much or his desserts are small That dares not put it to the touch to gain or lose it all ~James Graham |
#6
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
Or use a second sheet as a 'crib note/calc' sheet...
I was famous, once... http://www.heroscapers.com/community/blog.php?b=1715 Visit my site: http://www.superflycircus.com "I'm not cute...I'll mess you up!" ~Jake The Dog |
#7
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
I know there's a COUNTBLANK function that might be helpful. Something along the lines of if the COUNTBLANK function = 1 then don't do the calculation.
-insert signature here- |
#8
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
Thanks for all the suggestions. I think I have it
B14 =IF(A14=0,0,(A14-LARGE(A15:A23,1))) Logic: If Mileage input = 0 then Usage =0 Else Mileage = Existing Mielage -Largest (,1) (LARGE function) in range of prior week's inputs (A15:A23) . Turn off Zero results (non viewable) GENERATION 27: The first time you see this, copy it into your signature on any forum and add 1 to the generation. Social experiment |
#9
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
That works (unless someone takes a week-long vacation)
~Aldin, formulaicly He either fears his fate too much or his desserts are small That dares not put it to the touch to gain or lose it all ~James Graham |
#10
|
||||
|
||||
Re: Need help with an MS Excel Script/Formula
Yeah, I changed the second variable cell
B14 =IF(A14=0,0,(A14-LARGE(A15:$A$23,1))) Where A23 represents the absolute lowest usable cell, so the search parameter will search from the current line to the entire bottom. Thanks for the catch. GENERATION 27: The first time you see this, copy it into your signature on any forum and add 1 to the generation. Social experiment |
|
Thread Tools | Search this Thread |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Better Point Cost Method: NO FORMULA! | ArgosCap | Custom Units & Army Cards | 12 | April 15th, 2010 09:34 AM |
Drake's Flames Update - Formula D | InfinityMax | Other Games | 811 | February 19th, 2009 03:32 PM |
Formula for creating characters? | Tiak | Comic Hero Custom Creations | 2 | April 18th, 2008 09:28 AM |
Need Help in Excel. | Buddy Lee | General | 5 | August 24th, 2006 01:46 PM |
Excel with data anywhere? | Leon | Software | 18 | July 27th, 2006 09:55 AM |