Finding top 20% of spend dollars in a column
I can't remember the formula to find the top 20% of the dollars spent in a
I have 692 rows with dollar amounts I need to report the % (rank) compared
to everything else in the column.
Say we have this data in a column:
first copy the column to column B and sort it descending
In C1 enter:
In C2 enter:
=SUM($B$1:B2)/SUM(B:B) and copy down.
We now see:
75 98 10.34%
46 92 20.04%
59 75 27.95%
75 75 35.86%
32 68 43.04%
62 62 49.58%
98 61 56.01%
14 59 62.24%
46 49 67.41%
68 49 72.57%
49 46 77.43%
92 46 82.28%...Cannot find "Plot empty cells as" option in 2007
Excel has the option under
Tools -> Options -> Chart tab
Plot empty cells has
- Not plotted (leave gaps)
i cannot find the option in Excel 2007, after hunting off and on for 3 hours.
Where is it?
i've already dismissed the hack of filling values with the formula
Click on the chart
In the Chart Tools tabs open Design;
In the Data gro...Formula to find text and count it within an entire workbook
I have a workbook where there is nothing consistent or any named ranges. I
need to make a summary sheet and count everytime I have the word cable in the
entire workbook in column B. I have tried different variations of countif,
lookups and find but have not come up with a combination that works. My
restraint is that I can't have a macro for this workbook. I really need some
help, example below.
4 Wind Meter
Thanks in advance,
Message posted via http://www.officekb.com
=COUNTIF($a$1:$H$5000,"*Cable*&q...FRX - advanced options error
Client installed a new workstation (WinXP). Frx is running local
GP6(c-tree) data is on the server. We reinstalled Frx on the new workstation
and registered the dll's. The path on the spec set is pointing to the
correct place. I can see their files in the catalog but I get the advanced
options cannot be set when I go into Frx and the Item not found when I try to
generate the report.
Any help you can give is appreciated.
OK Guys & Gals, you are sooo helpful. Rather than search help/net for
how-to-do, I'm sure one of you can give me the answer.....
I want to launch a web-browser from my app. How do I find out which
web-browser the user has installed/is using. I suppose, similar question is
how to do same if I want the user to contact me via e-mail. Here's a code
fragment of what I currently do (red-faced, a little. But I'm not too proud
to ask for help & yes, I no WinExec is old-hat but so am I).
CString csPath = "SOFTWARE\\Microsoft\\Windows...Can't Find Macros
I have a Workbook (Workbook1) that references another Workbook
(Workbook2) that contains a number of routines. Workbook2 contains the
routines and the reference is made in the Workbook_Open Event of
There are a number of buttons on Workbook1 that run code in Workbook2
They are not Command Buttons as the original code was written for
Having now moved to Office 2003 (after moving through the various other
Office releases) the end users are experiencing problems. When they
click the buttons they receive a message saying that the macros can't
If...Recipient lookup doesn't find contacts
Version: Outlook 2003 (11.5608.5703)
OS: Win2K Server
Problem: When I'm creating a new email I expect to type a portion of my
recipient's name in the "To" textbox and see a short list of possible
recipients. On many occasions (including one this morning!) I've tried
to find someone that I know exists in my contacts list but it doesn't
find it. Then when I press the "To" button I see the contact right
there in my list of contacts.
In reading other posts I've tried the following without any luck:
1. I've checked that the contact I'm looking for ...Finding the two (2) lowest numbers in a range of numbers
Is there a simple way to find the two lowest numbers in a
range of numbers?
I'm familiar with the "MIN" command, which finds the
lowest number in a range, but I was hoping there would be
an easy way to find the TWO lowest numbers.
I suppose I could be less retarded than I usually am and post
R:R = range of data
k = kth smallest (ie 2 for second, 3 for third)
"Ron" <firstname.lastname@example.org> wrote in message
> Is there a simple way to find the two lowest numbers in a
> r...Find cells with conditional formats
Is there an easier way to find the cells with conditional formats than
going to each cell and selecting format, conditional formatting?
On Aug 28, 9:37=A0pm, mjones <mich...@quality-computing.com> wrote:
> Is there an easier way to find the cells with conditional formats than
> going to each cell and selecting format, conditional formatting?
Found it. It's under Edit, Go To, Special. Thanks.
My free "Formats & Styles" Excel add-in provides a count of CF cells that includes a count of cells with multiple conditions.
http://excelusergroup.org/m...How to find cells with links to other workbooks?
I have a spreadsheet which shows under the edit menu links to other
workbooks. I have tried to find where the links are without success. Is there
a easy wy of finding such links?
you may try
and search for Findlink.zip
> I have a spreadsheet which shows under the edit menu links to other
> workbooks. I have tried to find where the links are without success. Is there
> a easy wy of finding such links?
...how to find out number of emails sent by a user in a day?
I would like to create run some statistics on the usage patterns. Is
there a way to compute the number of emails send by each user in a
We are running Exchange Server 2003 .
I have enabled message tracking on the exchange server, and am
creating exchange logs in the w3c format.
thankx in advance.
You need third party tools.
James Chong (MVP)
MCSE | M+, S+, MCTS, Security+
On Mar 22, 4:30 pm, "hrushi" <hbh...@gmail.com> wrote:
> ...How to find the "fallback fonts" currently used by system?
Text drawing APIs (like TextOut) select a "fallback font" when glyph
is undefined in the realized logical font in DC.
For example, when rendering the following sentence using font Tahoma:
The phrase "information" in Japanese is "$B>pJs(B".
since Tahoma does not define $B>p(B and $BJs(B, a fallback font must be selected
automatically so that these are rendered properly.
My question is, how to find out the "fallback fonts" for current DC?
> Text drawing APIs (like TextOut) select a "fallback font" when glyph
> i...Finding a deleting duplicate emails in OL 2007
How can I find and delete duplicate messages in Outlook 2007?
Bruce, you wrote on Sun, 19 Aug 2007 11:00:57 -0700:
> How can I find and delete duplicate messages in Outlook 2007?
...Find errors in Excel.
I have this code:
For Each s In ActiveWorkbook.Sheets
For Each c In s.UsedRange.Cells
If IsError(c) Then
errval = c.Value
If errval = CVErr(xlErrRef) Then
c.Interior.ColorIndex = 3
It colors all cells red, if the contains the #REF! error. The problem is,
that my client would like it to select the first instans of the error and
stop. When he runs it a second time, it should stop with the second instans
and so on.
...Advanced Find View
is there a way to change the default advanced find view
to reflect other fields???
Thanks in advance?
...how to find data and list all of them in a cell.
I have one database table like this.
Col_A Col_B Col_C
name room cancel
person1 building1 no
person2 building1 yes
person3 building2 no
person4 building2 no
person5 building1 no
what I need is to list the name who don't cancel in building1 and fill in
the result would like to be : person1 , person5
pls. help me to solve this problem.
thank you so much
The simplest way would be to select the ...Find value in table based on two inputs
A1: Name B1: Dept C1: Age
A2: Henry B2: 501 C2: 28
A3: Stan B3: 201 C3: 19
A4: Mary B4: 101 C4: 22
A5: Larry B5: 301 C5: 29
how would write a formula to find the age of 'Mary' in dept '101'?
vlookup doesn't seem to support multiple compares.
or better still, put Name required in cell D1 and Dept required in E1
Change values in D1 and E1 for other selections....Advanced Find v3.0
One of our users was looking for a quick way to generate a list of
approximately 14 Accounts. Because these Accounts to not have an attribute
value in common that no other Accounts have, he created an Advanced Find with
one select for each Account on the Account name (a navchar attribute) using
the Contains option with a portion of the name as the value. He then
selected each row and when he clicked on Group Or, the first select statement
disappeared. And it did not apply the OR to the remaining statements. Each
successive clidk on the Group Or removed the select statement at the t...OWA Find Address Feature Shows Everybody
I am running Exchange 2000 and have several companies separated into their own OUs and Security groups so that each company cannot see one another (especially in Outlook). However, when the users use the To: button and then use the 'Find' feature, the query window displays users from the entire global address list. They cannot see any of these contacts from outlook on the lan because of security settings that were setup according to the ASP Deployment Guide for Exchange 2000 Server
Does anyone know how to resolve this?
Check the section "Restrict Outlook Web Access Lookups"...Where to find samples using WaveIn* API's ?
I'm looking to write a very basic program which uses these above noted
API's. Problem is that I don't know where to start. If anyone knows if
Microsoft has published any code samples would you please let me know ?
inderpaul_s1234 AT yahoo DOT com
To reply please remove the "1234" and translate the rest.
You can check ACMAPP sample in MSDN.
> Hi all.
> I'm looking to write a very basic program which uses these above noted
> API's. Problem is that I don't know where to start. If any...Wizard does not create an Event Procedure for find record combo bo
I've got a MS A 2003 mdb form within which I'd like a pull down to select a
record from a table. After going through the wizard the look-up does not work
and there is no "Event Procedure" created in the after update section...
I've used this in the past with a different installation of Access 2003
Thanks in advance!
It sounds like you might be trying to use the dreaded *LookupField* in
an Access table. You might visit:
for a discussion as to why you shouldn't.
To achieve the result you seem to be seeking, create a form b...Find all item codes for all items under one category
I have a list of thousands of UPCs with the category name in adjacent cells.
I need to build a formula to automatically populate a list of UPCs based on
the category selected in another worksheet in the workbook. The category
selection is already automatically selected from data that is imported in
another worksheet in the workbook.
This seems like it should be very simple, but I can't find anything online
that provides a simple way to do it. I only find manual ways to do it.
Thanks for any help out!
If desired, send your file to my address below. I will...Advanced find, relationships, mailmerge CRM3.0
We're running CRM3.0.
We are using relationships extensively which mean we can link accounts and
contacts together in lots of different ways (necessary because we're not a
This works ok, with some annoyances, and I know the data is in there, and so
is theoretically navigable. Practically though, it's a pain ...
I want to send a mailmerge letter to an "Account" for each Contact linked to
that account in a particular relationship (Acc1 ... Rel1 ... Blank ... Con1)
- including the account details, and some standard and customised details
from ...Outlook 2002: Can't Find (Acess) Folders From Outlook Today
I really used to like to navigate through my folders from Outlook
Today. Now, whenever I click a folder, I get a message stating that
Oulook is unable to locate. This happens no matter what I click on:
inbox, calander, notes, tasks, saved items, anything. I can navigate
to all of these folders from the menu, just not from Outlook Today. I
would appreciate any information as to how I might remedy this
Thanks in advance,
...Find all from a list
I have data that I past into a spread sheet that I download daily of over
1000 rows with names and data columns. I have a list of 15 people listed on
this list that I need to find their records, delete the others and end up
with only those 15 peoples data. What is the best way for me to accomplish
You can try this one with the names in column A
Enter all names in the array
Array("jelle", "ron", "dave")
you can also make a list on a worksheet if you want
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long