Returning only non zero effect groups
Here is a sample list (mine are much larger w/many more fields)
Name Amount Date
bob 100 04-05-05
bob -50 05-04-05
bob -25 05-05-05
sarah 200 04-03-05
sarah -200 04-06-05
dave 300 04-02-05
dave -150 04-27-05
dave -150 05-18-05
I only care about the values for the groups(name would be group in this
case) in which th...Calculating Returns
10-Oct-07 10000 Goog
20-Nov-08 2000 intc
29-Nov-08 4000 intc
21-Nov-09 goog -20000
21-Nov-09 intc -10000
How do I calculate returns for Goog and Intc individually, if I sold on the
21st Nov, and bought on three different dates?
I have a big list of such transactions. I want to calculate returns for
Your example is not clear. You do not show a before/after
I am a retired Reg Mgr for ING and held a series 7.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. Yo...Total by certain time period
I have three fields in a table, and want to determine the total of the
occurrences by a certain time period. The data I have looks like this.
FGC RDate Occurrences
81 2009/01 1
81 2009/02 1
81 2009/03 1
81 2009/04 1
81 2009/05 5
81 2009/06 84
81 2009/07 1
81 2009/08 6
81 2009/09 3
81 2009/10 1
81 2009/11 5
And I want it to look like this
Less Than 3 Months 9
3 Or More Months 91
6 Or More Months 7
9 Or More Months 2
12 Or More M...User Defined Function returning #Value!
I have a function that I created. When I test it in the Intermediate Window,
? Kountifs("Registered Nurse"), it returns a 12 which is correct.
I want to be able to use this function a my datasheet. I have included
basically the same function =Kountifs("Registered Nurse"). But, ont the
datasheet I receive a #Value! rather than the 12.
Does anyone have ideas why?
Why dont you post the UDF..
If this post helps click Yes
> I have a function that I created. When I test it in the Intermed...Vlookup and Trace Return Cell
Wondering how to have excel return the cell that the vlookup equation is
drawing from? For example, when my vlookup returns the value "45" within a
given table, how do I trace where that exact cell is that contains the 45?
When I try the formulas -> trace precedents, the vlookup only shows the
entire table that the function is using and not the cell of the value (the
45) it's returning.
Further, I should elaborate on my end goal and perhaps there is another,
better way: I have two tabs with identical set of values, but in different
orders (which is ho...Return Value from cells which match criteria (complex)
I have a worksheet I want to compute mileage on. Right now we're doing
everything manually, but I want to see if I can automate the process in Excel
without resorting to a lot (if any) VB code.
The formula needs to examine 4 columns for the start point, then 43 or so
columns for the destination point. From there it can reference another sheet
with the mileage between two points and insert the correct value.
The logic looks something like this: "Look for data in array a and in array
b then depending on which columns hold the data, lookup the corresponding
result in ...VLOOKUP Returning a copy of a cell
I want to use VLOOKUP to return not just the value in the cell but also the
formating of the cell. For instance, if this is my table
and I do a VLOOKUP(2, A:B, 2, FALSE) I will get "Sheep". However, if Sheep
is has a format that is for instance a strikethrough, then it will still show
up as "Sheep". How can I return the format as well? Is there a way to use
VLOOKUP to return a copy of the cell?
A formula can only return a value to a cell. It can not return formatting.
Microsoft Excel MVP...EXcluding Zeros from the average in a row
I am trying to average a row of numbers (F35:U35) that have numeric zeros in
some of the cells. However, I would like to exclude them, and the cells from
the calculation "=AVERAGE(F35:U35)". Is there a way to do that?
This array formula will do the job:
To be entered with <Shift><Ctrl><Enter> instead of <Enter>,
also if edited later.
Followup to newsgroup only please.
"Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse
news:9C2B0B65-1AF...Total Number of Invoices Smartlist or SSRS
I need to create a Smartlist or SSRS report that has the Customer Name,
Customer Balance (Overage),Total number of paid Invoices ,Total Cash
Received, Total number of unpaid invoices, Total $ unpaid invoices and can be
sorted by invoice date. What tables can i use im creating a smartlist or how
do i create an SSRS report for this?
I think this would be easier done in SSRS. Do you have any experience
with SSRS? If not, I suggest getting a book - SQL Server 2005
Reporting Services by Brian Larson. It will walk you through creating
reports. I keep it by my side always when creating SSRS...Pivot Table Totals
Is there any way that i can add an extra column to a pivot table that I can
add a function into?
We have 2 internal quality checks for our product. I have created a log
which shows what type of issues are discovered in the first and second
The pivot table in its most basic form shows the count of the different type
of issues found in the first QA check in the rows and the type of issues
found in the second QA check in the columns.
The nature of this layout means that looking at the Grand Total is
meaningless from a process analysis point of view. What I would ...printing zero values
I have a number of reports within a single spread sheet. Of these reports
only those relating to fields with poistive values need to be printed. How
can I avoid printing those fileds relating to zero vlaues?
Try - Tools, Options, View, and uncheck zero values in the Windows Options
"dave glynn" wrote:
> I have a number of reports within a single spread sheet. Of these reports
> only those relating to fields with poistive values need to be printed. How
> can I avoid printing those fileds relating to zero vlaues?
...Kit Item Return #2
Customer buys product in large quantities and puts them in kits to sell. So I
created an inventory item called Product which we put on POs and track
inventory on. I created Kit Items and added the Product to them with
quantities. The kits are put together as needed...meaning they are not in
Kits on the shelf.
It works great and tracking inventory is easy.....when I return a kit item.
It doesnt replenish the Product....it replenioshes the Kit.....The kit should
always be 0 on hand and the Product should be the one to deplete and
Any idea on how to make the return work correc...show total number of items in Inbox and Sent Items
My Outlook 2002 used to show (somewhere on the bottom of
the screen) the total number of items in the folder that
was highlighted, if the folder was "Inbox" or "Sent
Items". This was helpful because I try to keep the number
of items pretty low. Somehow (updates, patches, our IT
person???) this function has disappeared. Any ideas on
getting it back? Thanks
...Return a value from a list in another worksheet
I manage a sports league, and one of my most common tasks is to make
the season schedule at the beginning of the year, where each time has
to play another, at different times of the week. Each team has sent me
a set of availability restrictions, days× of the week that they
are unable to play. Thus, when I make my schedule, I have to take these
restrictions into account.
I have one separate worksheet for the game *schedule*, and another for
the *restrictions*. Thus in the worksheet "schedule" I have the home
team under column A, playing versus the away team on colum...total and subtotal positions wrong in excel
Excel is not positioning the sutotal and total lines correctly in my worksheet.
The information in the following MSKB article may help:
Multilevel subtotals are in the wrong position in Excel 2002 and in
Mary Buck wrote:
> Excel is not positioning the sutotal and total lines correctly in my worksheet.
Excel FAQ, Tips & Book List
...Incorrect totals in Pivot Table
I have created a calculated field in my pivot table. It works properly.
However, the column and row totals do not give intended results (sum of
displayed results). Rather they use the same formula on all data in that part
of the table. Here are the details:
Table shows payments by transaction for all customers (field name=AMT).
Customer may have + and - payments on any day. I need to show net payments
per day by customer. I then need to calculate 31% of net payment BUT only if
Pivot table: created 2-way table, calculating sum of pmts by customer by
day. works fine
ca...Count total minutes between 2 times
Cell 1: Feb 23 2010 4:11PM
Cell 2: Mar 1 2010 4:03PM
How do I count the total minutes between 2 cells without manually
If cell 1 is A1, and cell 2 is A2, use:
Format as a number
"cpliu" <firstname.lastname@example.org> wrote in message
> Cell 1: Feb 23 2010 4:11PM
> Cell 2: Mar 1 2010 4:03PM
> How do I count the total minutes between 2 cells without manually
...the message interface has returned an unknown error
When trying to delete an email from the inbox I receive
the message interface has returned an unknown error
Windows 2000 sp4
Outlook 2002 sp2
Ran inbox repair tool. (no errors)
Installed all updates from OFFICE update site.
please reply in group. emails directly to me will be printed and laughed at
in the breakroom
"PC" <email@example.com> wrote in message
> When trying to delete an email from the inbox I receive
> the follo...txt search box returns all records when wildcard is entered
I have used a simple textbox search originally created by Graham Thorpe
[Event Procedure]. When I open my search form If I type in and abbreviated
search text for example 206 vs. 206-030-987-101 It returns all of the records
in the database. If I search on 206-030-987-101 it will find that specific
record and display it, but all records are available.
I would like help with using the textbox search to bring up only those
records containing the exact match if one is available and not physically
being able to view any other records in the current form.
I would like to also be able to pe...How do I eliminate zeros from my Excel database?
I would like to average a good deal of data from my database, but do not know
how to eliminate the zeros so they are not factored into my calculations. I
would greatly appreciate any help on this! Thank you.
Use an array formula like the following:
Change the ranges to suit. Since this is an array formula, you
must press CTRL+SHIFT+ENTER rather than just ENTER the first time
you enter the formula and whenever you edit it later. If you do
this properly, Excel will display the formula enclosed in curly
Chip Pearso...Money 2002 Investment account Total does not show
Hi and first Thanks for helping. I set up two new
investment accounts for two different brokers. The first
account is Ok with all transactions and totals showing
correctly. The second account is setup the same as the
first and I have gone over and over the set up. All the
tranactions show for each of the indidvidual mutual funds
entered. The totals are displayed on each account. The
problem comes when I go to the accounts to use page and
the first account I set up shows the total and the second
account does not. Why does this total not show?
In microsoft.public.money, Jack wrote:
&...How-to not plot cells with zero value on graphs
Hey! ANyone know how to not plot values if they equal zero in the
hopeace's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28009
View this thread: http://www.excelforum.com/showthread.php?threadid=487862
I had this problem. I wrote a macro that scanned my data and wherever it
found a value of zero it deleted the cell contents. They then do not
plot on charts. My data values were complex formulas, so I wrote a
second macro to reinstate the formulas so ...Recalling item for RETURN
When I recall items for return, one of the problems is that the tender window
(F12) initially specifies a wrong $ amount by default in the credit card
field. To make it corect, I had to erase it and press "+" to set it to the
right value. For example, a gentleman bought 2 items and wanted to return
one this morning. I recalled the transaction, erased the item he kept and
left only the one he wants to return on the POS screen. Then I pressed F12
and I saw the entire transaction amount in the credit card field... the
amount to refund should have been about half of that total...Data Conversion Expression returning an #Error
Looking for some guidence on how to get this expression out sick bay!
The [Cut_Time] data field comes from a legacy system and may or may not have
data in it, may or may not be 0 thru 5 char in length, comes in formatted as
text data. The data represents a time stamp of an activity expressed in
The goal is to convert it to the DateTime format IF it has any entry at all;
IF blank just output a null.
CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",Fo...Adding a totally blank page for artwork
This is a real head scratcher, but the moral of the story I guess if that
luckily page numbers are counted regardless of being "linked" to the previous
section. The link is purely for formatting, as I understand, and this saved
But here's the thing that needs to be much easier to do.
I need to add a blank page I can control before the first page of a chapter
to put some artwork in.
The layout is artwork on the left (insert auto blank page if necessary), and
the text of the first chapter is on the right.
I can't use "first page dif...