New Formula

G

gsaenz

Well since you guys helped me out a lot last time I'm gonna test you out
again. Ok here is what I want to do now. I want to be able to figure
out my total miles done for one week. IE; On monday my truck had one
thousand miles, I did five loads (one on each day of the week) and on
Friday the truck has two thousand miles. I input the trucks milage and
I want a forumla that gives me the weeks milage (one thousand). One
column says start miles and column two says end miles. The problem is
that I dont always run five loads in one week, I might to ten or just
one.

Thanks again for all the help.
Gabriel Saenz
 
N

Norman Harker

Hi Gabriel!

I think we need a bit more data on your workbook layout.

Send a copy to me if you like.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi gsaenz!

How about:

=MAX(I6:I21)-H6

Also you might like to get rid of those ugly N/A!:
e.g.
=IF(F6=0,"",YourExistingFormula)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
G

gsaenz

Thanks a lot, this really helps me save a lot of time. I need to sit
down and learn more about VBA and Excel.

Take care and best regards.

Gabriel Saenz
 
N

Norman Harker

Hi Gabriel!

Thanks for thanks is always appreciated and shows Google Searchers
that something works "as advertised".

Re:
"I need to sit down and learn more about VBA and Excel."

Here's a bit of a summary collation of Excel resources posted
originally in response to a question on how to become an Excel Expert.

Perhaps the very best resource is yourself! Few experts have gained
their in-depth knowledge other than by banging their heads on the
keyboard trying to work out solutions to problems. It's sometimes
frustrating but even blind alleys teach you things and successes are
very rewarding. Don’t get too concerned though. The current hard stuff
becomes easier as you progress.

I use a program called NetCaptor that allows multi-tabbed copies of
Explorer and I have "Captor groups" set up in three blocks as follows:

Prime Sites
Secondary Sites
Microsoft Sites

PRIME

Chip Pearson:
http://www.cpearson.com/excel/topic.htm
Ranked number one equal because his index is so crystal clear and
content so broad

Dave McRitchie
http://www.mvps.org/dmcritchie/excel/xlindex.htm
Ranked number one for breadth of content but the index is necessarily
more difficult.

John Walkenbach
http://j-walk.com/ss/excel/index.htm
Lots of useful Add-ins and downloadable examples from one of the most
popular writers of Excel texts.

Then there's a special link that is worth going to immediately:

SPECIAL

Ron de Bruin
www.rondebruin.nl/Google.htm
Ron has an Add-In that you can download free of charge. It installs a
Google search tool under your Help menu in Excel. If you don't Google
search Excel problems now, you will soon do so when you get the hang
of it.

SECONDARY

I've left out many real gems on the web. I plead guilty and ask for
the need for selectivity to be taken into account. Plus, even with
NetCaptor it does take time to download all the front pages. But just
briefly and all with different strengths in different areas:

http://www.bmsltd.co.uk/Excel/Default.htm
http://contextures.com/tiptech.html
http://www.erlandsendata.no/english/index.htm
http://www.mvps.org/vb/
http://www.decisionmodels.com/calcsecretsj.htm
http://www.vbapro.com/
http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm
http://www.pcmag.com/category2/0,4148,29,00.asp
http://www.tushar-mehta.com/
http://www.geocities.com/jonpeltier/Excel/Charts/index.html


MICROSOFT

http://support.microsoft.com/default.aspx?scid=fh;en-us;kbinfo
http://www.microsoft.com/downloads/search.asp?
http://www.microsoft.com/
http://support.microsoft.com/default.aspx?scid=/support/Excel/Content/CoolTips/cool.asp#E10E28

NEWSGROUPS

But don't forget the newsgroups. It's the number one learning tool on
the internet. Post a question and it usually doesn't take more than an
hour to get a reply or three. Posters are all volunteers who give
their expertise freely and cheerfully. It's not total altruism at work
because they know that it is also by far the best way to extend their
skills, keep them at their finger-tips and solve the particular
problems they have (or might not realize they have!) Most often you'll
get a solution and / or a reference to the place to go that covers the
problem definitely (well.....) or in depth.

You can also gain very valuable expertise by "lurking" and seeing what
replies are to various questions that interest you and even more so by
attempting answers yourself even if at first you don't send but see if
you are right. But don't worry about making bloopers as we all do that
and I've not yet known anyone treated other than with respect when
they are trying to help or are being helped.

The top three newsgroups in terms of usage are:

microsoft.public.excel.misc
microsoft.public.excel.programming
microsoft.public.excel.worksheet.functions

Post only to one group and select the one you think most likely to be
the right one for your query. If in doubt use misc but not to worry
because you'll probably get an answer on whatever group you go for.
But don't attach files as this is very much frowned on because of
download times and costs if it became too prevalent and because of
fear of viruses etc. Generally the very act of describing your problem
triggers the solution before you finish asking the questions (and
sometimes triggers it just as you click "send" <vbg>)

Make sure that you connect through:

msnews.microsoft.com

It's faster and quicker to update. Also make sure you read Chip
Pearson's and Dave McRitchie's Hints for new posters. They provide a
wealth of useful information on getting the most out of newsgroups.

BOOKS

But there's still no substitute for some good hard copy text for
structured and comprehensive coverage of the area. Here, you might do
some preliminary
research on what's available at:

http://www.amazon.com

Don't forget to use their search tool. Most texts are available
through them at a discount and it's well worth reading the Author
reviews and the reviews posted by readers. Don't just go for star
rating or even Amazon sales ranking but if it sounds good, rates good
and ranks good (established texts only) it's probably worth having a
good look at it. Amazon are quite cheap and pretty fast; that can be
quite important when the good bookshops are hard to find and / or
carry limited stock and / or (like Australia) if it takes at least 6
months before the new title is floated across the ocean on a raft.

John Walkenbach is regarded as one of the leading writers on Excel and
many swear by (and occasionally at) his "trilogy" Excel 2002 Bible,
Excel 2002 Power Programming with VBA and Excel 2002 Formulas. Don't
worry about your Excel Version number as the earlier Version
differences are clearly signposted. But there are other extremely good
texts that are worth browsing before buying as it is a matter of
taste. For a downloadable recent list of the range of current material
available go to:

Debra Dalgleish
http://contextures.com/tiptech.html
Debra maintains the current list of published texts with
classification, titles, prices and ISBNs.

FINALLY

Don't forget the online Help that comes with Excel. Too many people
deride it as being written for geeks by geeks. It does have it's
moments but generally it is getting better and better with each
succeeding version.

That should keep you busy for a while. But remember that one of the
main joys of Excel is that it is so broad and so deep that you will
never know it all. Also it has so many applications to different
disciplines and sub-disciplines that you will never stop finding
hidden depths and applications.





--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
G

gsaenz

I'll have to take some time, sit down and go through some of those
sites. I am running into one problem though. When I put the if
statement it seems to forget about the vlookup. I sent you a copy of
the .xls to '(e-mail address removed)'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top