Heroscapers
Go Back   Heroscapers > Off-Topic > General
General Random thoughts and ideas. "General" does not mean random drivel, nonsense or inane silliness.


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old April 29th, 2009, 11:30 AM
Dennys's Avatar
Dennys Dennys is offline
I'm my own grandpa
 
Join Date: December 20, 2006
Location: NY - Middletown
Posts: 1,510
Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby!
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
Reply With Quote
  #2  
Old April 29th, 2009, 11:44 AM
Onacara Onacara is offline
has been BANNED
 
Join Date: January 1, 2007
Location: Pony Street
Posts: 16,992
Images: 1
Blog Entries: 27
Onacara is a puppet of Ne-Gok-Sa
Re: Need help with an MS Excel Script/Formula

Quote:
Originally Posted by Dennys View Post
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
You could Conditionally Format it to count Null Cells as 0 can't you?
Reply With Quote
  #3  
Old April 29th, 2009, 11:51 AM
Dennys's Avatar
Dennys Dennys is offline
I'm my own grandpa
 
Join Date: December 20, 2006
Location: NY - Middletown
Posts: 1,510
Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby!
Re: Need help with an MS Excel Script/Formula

Quote:
Originally Posted by Onacara View Post
Quote:
Originally Posted by Dennys View Post
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
You could Conditionally Format it to count Null Cells as 0 can't you?
Probably, it wouldn't matter. I just need it to recognize the next valid odometer reading for the calculation.

GENERATION 27:
The first time you see this, copy it into your signature on any forum and add 1 to the generation. Social experiment
Reply With Quote
  #4  
Old April 29th, 2009, 11:59 AM
SuperflyTNT's Avatar
SuperflyTNT SuperflyTNT is offline
Sheeple of the Codex of Ultimate Wisdom and Morality
 
Join Date: August 7, 2008
Location: At the Superfly Circus, of course!
Posts: 5,123
Images: 20
Blog Entries: 10
SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death!
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
Reply With Quote
  #5  
Old April 29th, 2009, 12:00 PM
Aldin's Avatar
Aldin Aldin is offline
Site Admin & Professional SideBoarder
 
Join Date: September 22, 2006
Location: TN - Nashville
Posts: 13,537
Images: 1
Blog Entries: 4
Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer
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
Reply With Quote
  #6  
Old April 29th, 2009, 12:02 PM
SuperflyTNT's Avatar
SuperflyTNT SuperflyTNT is offline
Sheeple of the Codex of Ultimate Wisdom and Morality
 
Join Date: August 7, 2008
Location: At the Superfly Circus, of course!
Posts: 5,123
Images: 20
Blog Entries: 10
SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death! SuperflyTNT is hot lava death!
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
Reply With Quote
  #7  
Old April 29th, 2009, 12:04 PM
nyys's Avatar
nyys nyys is offline
quoting myself - insanity beckons
 
Join Date: June 21, 2007
Location: MA - South Shore
Posts: 15,222
Images: 2
nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth nyys is a man of the cloth
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.

Boston Marathon 4/15/13 3:39:41
Last Race: Naragansett Running Festival 7/21/13 - Half Marathon - 1:50:01
Next Race: KeyBank Vermont Marathon 5/25/14
Reply With Quote
  #8  
Old April 29th, 2009, 12:19 PM
Dennys's Avatar
Dennys Dennys is offline
I'm my own grandpa
 
Join Date: December 20, 2006
Location: NY - Middletown
Posts: 1,510
Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby!
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
Reply With Quote
  #9  
Old April 29th, 2009, 12:24 PM
Aldin's Avatar
Aldin Aldin is offline
Site Admin & Professional SideBoarder
 
Join Date: September 22, 2006
Location: TN - Nashville
Posts: 13,537
Images: 1
Blog Entries: 4
Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer Aldin is a wielder of the Ban Hammer
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
Reply With Quote
  #10  
Old April 29th, 2009, 03:39 PM
Dennys's Avatar
Dennys Dennys is offline
I'm my own grandpa
 
Join Date: December 20, 2006
Location: NY - Middletown
Posts: 1,510
Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby! Dennys rolls all skulls baby!
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
Reply With Quote
Reply

Go Back   Heroscapers > Off-Topic > General
Thread Tools Search this Thread
Search this Thread:

Advanced Search
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 10:34 AM
Drake's Flames Update - Formula D InfinityMax Other Games 811 February 19th, 2009 04:32 PM
Formula for creating characters? Tiak Comic Hero Custom Creations 2 April 18th, 2008 10:28 AM
Need Help in Excel. Buddy Lee General 5 August 24th, 2006 02:46 PM
Excel with data anywhere? Leon Software 18 July 27th, 2006 10:55 AM


All times are GMT -4. The time now is 10:12 PM.

Heroscape background footer

Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
User Alert System provided by Advanced User Tagging (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.