```Hi, I am using the newest version of Excel on Windows 7, and I need some help
with a complex calculation.

In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
\$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
percentage for jobs in those categories.  So, for the category of \$1-\$99,
lets say there are two cells with amounts in that range, A1 and A2.  Their
percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
would then be 50%.  I don't need any help with something that simple, but
when there are hundreds of large numbers involved, I need Excel to
automatically calculate this.

My thoughts on what the equation would look like in layman's terms would be:

AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)

With the above example, and this equation, I would be left with the average
of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
that this makes sense, because I am in desperate need of figuring this out.
I have no idea how to properly enter such an equation into Excel.

Thanks so much,

Aaron
```
 0
Utf
1/27/2010 7:08:14 PM
excel.misc 78881 articles. 5 followers.

7 Replies
498 Views

Similar Articles

[PageSpeed] 56

```perhaps add a column and use an if statement to categorize column a, sort by
this new column, and then use data>subtotal to average column a at each
change in category

"Hoov" wrote:

> Hi, I am using the newest version of Excel on Windows 7, and I need some help
> with a complex calculation.
>
> In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
> B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
> cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
> \$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
> percentage for jobs in those categories.  So, for the category of \$1-\$99,
> lets say there are two cells with amounts in that range, A1 and A2.  Their
> percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
> would then be 50%.  I don't need any help with something that simple, but
> when there are hundreds of large numbers involved, I need Excel to
> automatically calculate this.
>
> My thoughts on what the equation would look like in layman's terms would be:
>
> AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
>
> With the above example, and this equation, I would be left with the average
> of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
> that this makes sense, because I am in desperate need of figuring this out.
> I have no idea how to properly enter such an equation into Excel.
>
> Thanks so much,
>
> Aaron
```
 0
Utf
1/27/2010 7:14:01 PM
```Try this array formula** :

=AVERAGE(IF(A1:A100>=1,IF(A1:A100<=99,B1:B100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Or, use some cells to hold the boundaries:

D1 = 1...E1 = 99
D2 = 100...E2 = 199
D3 = 200...E3 = 299
etc
etc

=AVERAGE(IF(A\$1:A\$100>=D1,IF(A\$1:A\$100<=E1,B\$1:B\$100)))

Copy down as needed

--
Biff
Microsoft Excel MVP

"Hoov" <Hoov@discussions.microsoft.com> wrote in message
news:4262941D-C503-499D-AB88-4EC9BD8B47EC@microsoft.com...
> Hi, I am using the newest version of Excel on Windows 7, and I need some
> help
> with a complex calculation.
>
> In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
> B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
> cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
> \$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
> percentage for jobs in those categories.  So, for the category of \$1-\$99,
> lets say there are two cells with amounts in that range, A1 and A2.  Their
> percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
> would then be 50%.  I don't need any help with something that simple, but
> when there are hundreds of large numbers involved, I need Excel to
> automatically calculate this.
>
> My thoughts on what the equation would look like in layman's terms would
> be:
>
> AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
>
> With the above example, and this equation, I would be left with the
> average
> of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
> that this makes sense, because I am in desperate need of figuring this
> out.
> I have no idea how to properly enter such an equation into Excel.
>
> Thanks so much,
>
> Aaron

```
 0
T
1/27/2010 7:21:00 PM
```I am not sure how to write the ">= but <" .  I'm not proficient in Excel, so
I'm not real familiar with your explanation.  An actual formula example would

"joemeshuggah" wrote:

> perhaps add a column and use an if statement to categorize column a, sort by
> this new column, and then use data>subtotal to average column a at each
> change in category
>
> "Hoov" wrote:
>
> > Hi, I am using the newest version of Excel on Windows 7, and I need some help
> > with a complex calculation.
> >
> > In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
> > B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
> > cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
> > \$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
> > percentage for jobs in those categories.  So, for the category of \$1-\$99,
> > lets say there are two cells with amounts in that range, A1 and A2.  Their
> > percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
> > would then be 50%.  I don't need any help with something that simple, but
> > when there are hundreds of large numbers involved, I need Excel to
> > automatically calculate this.
> >
> > My thoughts on what the equation would look like in layman's terms would be:
> >
> > AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
> >
> > With the above example, and this equation, I would be left with the average
> > of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
> > that this makes sense, because I am in desperate need of figuring this out.
> > I have no idea how to properly enter such an equation into Excel.
> >
> > Thanks so much,
> >
> > Aaron
```
 0
Utf
1/27/2010 7:30:02 PM
```im sure there is a better way, but this is how i would do it...

assuming column a has your \$ amounts and column b is your % amounts starting
in row 2...

in columns c through n, enter the following formulas respectively and paste
down for each row:
=SUM(D2:N2)
=AND(A2>99,A2<=199)*2
=AND(A2>199,A2<=299)*3
=AND(A2>299,A2<=399)*4
=AND(A2>399,A2<=499)*5
=AND(A2>499,A2<=599)*6
=AND(A2>599,A2<=699)*7
=AND(A2>699,A2<=799)*8
=AND(A2>799,A2<=899)*9
=AND(A2>899,A2<=999)*10
=(A2>999)*11

when all is said and done column c will make your categories based on the
total it provides.  sort the spreadsheet by this column.  then use the
subtotal option in the data menu.  you would want to use column c for "at
each change in", average for "use function", and column b for "add subtotal
to"

"Hoov" wrote:

> I am not sure how to write the ">= but <" .  I'm not proficient in Excel, so
> I'm not real familiar with your explanation.  An actual formula example would
>
> "joemeshuggah" wrote:
>
> > perhaps add a column and use an if statement to categorize column a, sort by
> > this new column, and then use data>subtotal to average column a at each
> > change in category
> >
> > "Hoov" wrote:
> >
> > > Hi, I am using the newest version of Excel on Windows 7, and I need some help
> > > with a complex calculation.
> > >
> > > In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
> > > B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
> > > cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
> > > \$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
> > > percentage for jobs in those categories.  So, for the category of \$1-\$99,
> > > lets say there are two cells with amounts in that range, A1 and A2.  Their
> > > percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
> > > would then be 50%.  I don't need any help with something that simple, but
> > > when there are hundreds of large numbers involved, I need Excel to
> > > automatically calculate this.
> > >
> > > My thoughts on what the equation would look like in layman's terms would be:
> > >
> > > AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
> > >
> > > With the above example, and this equation, I would be left with the average
> > > of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
> > > that this makes sense, because I am in desperate need of figuring this out.
> > > I have no idea how to properly enter such an equation into Excel.
> > >
> > > Thanks so much,
> > >
> > > Aaron
```
 0
Utf
1/27/2010 7:59:01 PM
```sorry...use these in columns c through n...i was missing one in the previous
string

=SUM(D2:N2)
=(A2<=99)*1
=AND(A2>99,A2<=199)*2
=AND(A2>199,A2<=299)*3
=AND(A2>299,A2<=399)*4
=AND(A2>399,A2<=499)*5
=AND(A2>499,A2<=599)*6
=AND(A2>599,A2<=699)*7
=AND(A2>699,A2<=799)*8
=AND(A2>799,A2<=899)*9
=AND(A2>899,A2<=999)*10
=(A2>999)*11

"joemeshuggah" wrote:

> im sure there is a better way, but this is how i would do it...
>
> assuming column a has your \$ amounts and column b is your % amounts starting
> in row 2...
>
> in columns c through n, enter the following formulas respectively and paste
> down for each row:
> =SUM(D2:N2)
> =AND(A2>99,A2<=199)*2
> =AND(A2>199,A2<=299)*3
> =AND(A2>299,A2<=399)*4
> =AND(A2>399,A2<=499)*5
> =AND(A2>499,A2<=599)*6
> =AND(A2>599,A2<=699)*7
> =AND(A2>699,A2<=799)*8
> =AND(A2>799,A2<=899)*9
> =AND(A2>899,A2<=999)*10
> =(A2>999)*11
>
>
> when all is said and done column c will make your categories based on the
> total it provides.  sort the spreadsheet by this column.  then use the
> subtotal option in the data menu.  you would want to use column c for "at
> each change in", average for "use function", and column b for "add subtotal
> to"
>
>
>
>
> "Hoov" wrote:
>
> > I am not sure how to write the ">= but <" .  I'm not proficient in Excel, so
> > I'm not real familiar with your explanation.  An actual formula example would
> > be the most helpful.
> >
> > "joemeshuggah" wrote:
> >
> > > perhaps add a column and use an if statement to categorize column a, sort by
> > > this new column, and then use data>subtotal to average column a at each
> > > change in category
> > >
> > > "Hoov" wrote:
> > >
> > > > Hi, I am using the newest version of Excel on Windows 7, and I need some help
> > > > with a complex calculation.
> > > >
> > > > In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
> > > > B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
> > > > cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
> > > > \$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
> > > > percentage for jobs in those categories.  So, for the category of \$1-\$99,
> > > > lets say there are two cells with amounts in that range, A1 and A2.  Their
> > > > percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
> > > > would then be 50%.  I don't need any help with something that simple, but
> > > > when there are hundreds of large numbers involved, I need Excel to
> > > > automatically calculate this.
> > > >
> > > > My thoughts on what the equation would look like in layman's terms would be:
> > > >
> > > > AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
> > > >
> > > > With the above example, and this equation, I would be left with the average
> > > > of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
> > > > that this makes sense, because I am in desperate need of figuring this out.
> > > > I have no idea how to properly enter such an equation into Excel.
> > > >
> > > > Thanks so much,
> > > >
> > > > Aaron
```
 0
Utf
1/27/2010 8:10:10 PM
```The array formula worked beautifully!  No flaws whatsoever!  Thank you so much!

"T. Valko" wrote:

> Try this array formula** :
>
> =AVERAGE(IF(A1:A100>=1,IF(A1:A100<=99,B1:B100)))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> Or, use some cells to hold the boundaries:
>
> D1 = 1...E1 = 99
> D2 = 100...E2 = 199
> D3 = 200...E3 = 299
> etc
> etc
>
> =AVERAGE(IF(A\$1:A\$100>=D1,IF(A\$1:A\$100<=E1,B\$1:B\$100)))
>
> Copy down as needed
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Hoov" <Hoov@discussions.microsoft.com> wrote in message
> news:4262941D-C503-499D-AB88-4EC9BD8B47EC@microsoft.com...
> > Hi, I am using the newest version of Excel on Windows 7, and I need some
> > help
> > with a complex calculation.
> >
> > In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In cells
> > B1-B100, I have percentages ranging from 0%-100%.  I want to break out the
> > cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
> > \$200-\$299, and so on up to \$1000.  Then, I need to calculate the average
> > percentage for jobs in those categories.  So, for the category of \$1-\$99,
> > lets say there are two cells with amounts in that range, A1 and A2.  Their
> > percentages in B1 and B2 are 40% and 60%, respectively.  Well, the average
> > would then be 50%.  I don't need any help with something that simple, but
> > when there are hundreds of large numbers involved, I need Excel to
> > automatically calculate this.
> >
> > My thoughts on what the equation would look like in layman's terms would
> > be:
> >
> > AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
> >
> > With the above example, and this equation, I would be left with the
> > average
> > of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really hope
> > that this makes sense, because I am in desperate need of figuring this
> > out.
> > I have no idea how to properly enter such an equation into Excel.
> >
> > Thanks so much,
> >
> > Aaron
>
>
> .
>
```
 0
Utf
1/27/2010 8:31:02 PM
```You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Hoov" <Hoov@discussions.microsoft.com> wrote in message
news:35D91B80-6C38-4F21-90F5-0F5AAEAC9787@microsoft.com...
> The array formula worked beautifully!  No flaws whatsoever!  Thank you so
> much!
>
> "T. Valko" wrote:
>
>> Try this array formula** :
>>
>> =AVERAGE(IF(A1:A100>=1,IF(A1:A100<=99,B1:B100)))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> SHIFT
>> key then hit ENTER.
>>
>> Or, use some cells to hold the boundaries:
>>
>> D1 = 1...E1 = 99
>> D2 = 100...E2 = 199
>> D3 = 200...E3 = 299
>> etc
>> etc
>>
>> =AVERAGE(IF(A\$1:A\$100>=D1,IF(A\$1:A\$100<=E1,B\$1:B\$100)))
>>
>> Copy down as needed
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Hoov" <Hoov@discussions.microsoft.com> wrote in message
>> news:4262941D-C503-499D-AB88-4EC9BD8B47EC@microsoft.com...
>> > Hi, I am using the newest version of Excel on Windows 7, and I need
>> > some
>> > help
>> > with a complex calculation.
>> >
>> > In cells A1-A100, I have dollar amounts ranging from \$1-\$1000.  In
>> > cells
>> > B1-B100, I have percentages ranging from 0%-100%.  I want to break out
>> > the
>> > cells in A1-A100 in several groups, like follows: \$1-\$99, \$100-\$199,
>> > \$200-\$299, and so on up to \$1000.  Then, I need to calculate the
>> > average
>> > percentage for jobs in those categories.  So, for the category of
>> > \$1-\$99,
>> > lets say there are two cells with amounts in that range, A1 and A2.
>> > Their
>> > percentages in B1 and B2 are 40% and 60%, respectively.  Well, the
>> > average
>> > would then be 50%.  I don't need any help with something that simple,
>> > but
>> > when there are hundreds of large numbers involved, I need Excel to
>> > automatically calculate this.
>> >
>> > My thoughts on what the equation would look like in layman's terms
>> > would
>> > be:
>> >
>> > AVG ((IF A1>=1 but <100, B1) + (IF A2>=1 but <100, B2)
>> >
>> > With the above example, and this equation, I would be left with the
>> > average
>> > of B1 (or 40%) + B2 (or 60%), and the answer would be 50%.  I really
>> > hope
>> > that this makes sense, because I am in desperate need of figuring this
>> > out.
>> > I have no idea how to properly enter such an equation into Excel.
>> >
>> > Thanks so much,
>> >
>> > Aaron
>>
>>
>> .
>>

```
 0
T
1/28/2010 3:36:36 AM

Similar Artilces:

Faulty cost basis calculation
I just re-installed Money 2003 Delux afyter a hardware problem, and am using a backup copy of the Money file. I hav eone investment that shows the correct purchase amounts and quantities, but has calculated the cost basis incorrectly. ( i.e. purchase 4000 shares at 2.35, cost basis \$52,000 when it should be about \$11,000). I can not find any way to edit the cost basis, or a way to get this program to re-calculate it correctly. Anyone else have this problem and solve it? In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >I just re-installed Money 200...

Hello, I'm a complete newbie trying to design a relatively simple data base for organizing choir music. I need to store arranger and composer names and be able to search for them. I will not need to track or display first and last names separately, so I would rather not use separate fields. But I need to have the data input consistently. Is there a way to create an input mask or validation rule to insure "Last name, First Name" format? Thank you, -- Leslie On Thu, 18 Oct 2007 13:12:00 -0700, leslie1165 wrote: > Hello, > > I'm a complete newbie trying t...

Need help with modifying VLookUp
I need some help with modifying a VLOOKUP function. I have the following data in cell range E1:G9. Col_E Col_F Col_G ===== ===== ===== Richard 1 A Richard 2 B Richard 3 C Sue 4 D Sue 5 E Sue 6 F Tom 7 G Tom 8 H Tom 9 I Cell A1 will have either one of the 3 names: Richard, Sue, Tom Then, in cell range A15:B17, I want to show the associated values of either name: - F1:G3, or - F4:G6, or - F7:G9 So, if A1 = ...

Question about total cast basis calculation for an investment acco
I am using Money 2004 to track my mutual fund investments, among other things. I had the portfolio manager options set to show closed positions. When configured as such, the the cost basis from the closed positions was added into the total cost basis for the investment. In my case, the closed positions were a result of a non-taxable sell and reinvest into another investment. Long story short, some money shows up twice in the total cost basis calculation. After clearing the option to show closed positions, the total cost basis calculation appears to be correct. I would expect to see...

Help with labels and lines in chart
Hi all i am tracking a few things and i wanted to make some objects in the my chart reposition dynamicly as i change the scale i dont care if i have to do a little clean up but just that the postions stay correct i have attached a picture any suggestion would be greatly appreshated +-------------------------------------------------------------------+ |Filename: Unti.png | |Download: http://www.excelforum.com/attachment.php?postid=5056 | +-------------------------------------------------------------------+ -- crash893 ---------------...

This is my first time, and go figure, Verizon is no help (my ISP). How can I make it work. I'm dumfounded. I created the html files in Publisher, but when I upload them using WS FTP, only the default (1st) page works, and none of the links work. How about you tell us what version of Windows and Publisher you are using? -- I'm going to guess you are using version 2002. I recommend you review my 2002 tutorial at www.davidbartosik.com/ppt.htm I also recommend you use our forum at microsoft.public.publisher.webdesign -- David Bartosik - Microsoft MVP Visit www.davidbartosi...

Net Worth increase calculated incorrectly?!
I've noticed what seems to be a bug to me. Let say for example that my net worth was negative \$5,000 and then the next month it became positve \$7,500, money then says on it's monthly report that my net worth went up 25% in the last month. Using some sort of logic, wouldn't it be more like 225% ? "Dan Andersen" <isdanreally@hotmail.com> wrote in message news:ux63x34cEHA.2504@TK2MSFTNGP12.phx.gbl... > I've noticed what seems to be a bug to me. Let say for example that my net > worth was negative \$5,000 and then the next month it became positve \$7,500, ...

Hello I am faced with a rather difficult task and dilemma. Here is my scenario: My company currently hosts Microsoft Exchange 2003 with a third-party solution that hosts several thousand accounts from different companies on the same exchange server. We have built an in-house exchange server that is ready for deployment. I need to know the best and most seamless way to transition from the outsourced company to our in-house exchange so that mail delivery is not disrupted. The email address and domain name will remain the same. I have contacted several companies that offer tools for e...

HELP RE: DISPLAY CHECK NUMBER ON PAYEE REPORT WITH
How do I get a historic payment reoport for a specific payee including check number, date, amount, and payee name? ie: "Car Loan Payment history for the period 1/1/01 thru today" Payee: Check # : Date: Amount: (and perhaps a running total or a final total) I think I have tried all the built in reports and can't find one to do this or modify existing to do this. I see this information in the "view payee details" window, listed in the format I require but when I select PRINT all that prints is the bar graph that is also displayed on this page. I ca...

How do I paste a email screen shot into the message area please?
Hey Everyone: One of my users has a problem which is hard to explain in words. I need to paste the screen shot in this space that he sent me via email, and I'm unable to do so. How do I paste a email screen shot into this message area please? Thanks in advance GP Guy Not possible, but you can use any free image hosting service on the net and let us know where we check it out. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "GP Guy in AZ" wrote: > Hey Everyone: > > One of my users has a pro...

Help #7
Sir, Question1. How to recover or check consitency using eseutil and other console commands? Question 2. At Present Exchange Server is in Live containing Database of 110 users Can We do clustering in exchange to fail over recovery. Did it will affect the existing data base Waiting for your reply regards Sathish kumar Hi, Eseutil is located at "C:\Program Files\Exchsrvr\BIN". This utility can be used for several things go to this directory then run eseutil, it will display all the options available. You can do exchange cluster, but you need to make sure you got Exchange E...

Help with this code please !!!!!!!
I have use the code set below in other files but in this new file I am getting an error on "Cells.Select". "Select method of Range class failed" Any idea whats going on? Sheets("Completions Summary").Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range("A4").Select are you sure you have the sheet name correct? no need to select, i'd use something like this: With Worksheets("Completions Summary").Cells .Copy ...

help with weekdays between two dates
A little rusty with access - been away for a while. Wondering if someone could help me with determining the weekdays between two dates. a simple function of somekind using the date or count functions? Be great if it did stat holidays too but I know that's asking more than is possible? JT "Jim" <jimt@pioneers.ca> wrote in message news:2rWAj.66322\$pM4.15431@pd7urf1no... >A little rusty with access - been away for a while. > > Wondering if someone could help me with determining the weekdays between > two dates. > > a simple function of somekind us...

Excel help>>>>>
Hey all :) i'm new to this forum and have a question. I'm working with windows and doors in wood in my own firm. I use excel to calculate the price and also to calculate the windows frames. So here is the question : is it in any way to get excel to draw the frames for the windows based on what i set for Width and Height or is it not possible ?? Something like this : http://www.windowmaker.com/eng/udd2.htm Thanks for any reply. :) -- Globel ------------------------------------------------------------------------ Globel's Profile: http://www.excelforum.com/member.php?action...

Date Help
I have linked two columns on a worksheet so that when I type in the date in cell C6 it also appears in cell J6. I entered =\$C\$6 in J6, and this works except sometimes I don’t need to use the date cells. My problem is in cell J6 the date appears as 1/0/00 until I type the date into cell C6. What am I doing wrong? Thanks, Malcolm Hi, use this formula instead =if(C6="","",\$C\$6) "Malcolm" wrote: > I have linked two columns on a worksheet so that when I type in the date in > cell C6 it also appears in cell J6. I entered =\$C\$6 in J6, and ...

averaging without zero
To All: I need help with following what is the formula for obtaining of three numbers when there are zeros in the column 31.26 31.90 31.83 0 0 0 How do I average the above without zero making this six lnumber ro average . Or how can I use only the two lower numbers. ThanKs Zeta Zeta, =AVERAGE(IF(A1:A6>0,A1:A6)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "zetatstrat" <zetatstrat@discussions.microsoft.com> wrote in message news:9C3D7586-55A7-4E2A-91FF-035A3BEBFDA9@micros...

Calculating entries in a group
I have a database that lists all applicants, and I have a report that sorts them according to which Institution they are attending. How can I have the report display the number of applicants that are attending each Institution. For example , Four applicants from Harvard, 10 Applicants from Columbia, ect. Use this query --- SELECT YourTable.[Institution], Count(YourTable.[Institution]) AS [CountOfInstitution] FROM YourTable GROUP BY YourTable.[Institution]; -- KARL DEWEY Build a little - Test a little "amandap83" wrote: > I have a database that lists all applicants, and ...

Help with debugging query (Rolling Average)
Hi I'm trying to do a rolling average on data that is ordered by a date time column. I'm after adivce about how best to do this, I've tried this - SELECT Time, ValueX, (HOUR(Time) * 3600 + Minute(Time) * 60 + Second(Time)) As TickTock (SELECT Avg(ValueX) FROM My_Table WHERE TickTock Between (TickTock) AND (TickTock) + 30) AS RollingAverage FROM My_Table; Basically my thinking was that it would be easier to work on this datetime data as Seconds, So that my rolling average can be based on 10, at 3 second intervals When I try this I get nothing in my RollingAverage box and also a...

Help with query-dont want to see dupes
I've made a query to find customers that have ordered a certain product between a period of time. Some customers order a lot so they'll appear multiple times but I only want to show each customer once. The field to compare is ContactID On Tue, 10 Apr 2007 10:02:51 -0700, "Rob" <nospam@nospam.com> wrote: >I've made a query to find customers that have ordered a certain product >between a period of time. Some customers order a lot so they'll appear >multiple times but I only want to show each customer once. > >The field to compare is Contac...

How do i calculate a 50 moving averager?
When generating a trendline, the original data still remains. Is there a way to get rid of the data (other than changing the color to white) and simply having the trendline? I was thinking if it is not an option i can calculate the moving averager and merely graph that column. Let me know what is the best way to go about this....Thank you I don't think you can maintain the trendline if you remove the underlying data. You can simply create a new column to graph which is the moving average by using the average command and then dragging it down. By this I mean lets say you had a ...