Calculating Between Values

Hope you can help me!

I have a column on one spreadsheet called "Platts Ports"

Now, I want to analize column E. I want to be able to pick out all the
values between 0 & 0 (>0 &<2). I then want to say the following:

Where value = between 0-2 (COUNT) then add up column F where all these
fields apply. Then divide COUNT(E) by the total of F( the sum we just
calculated)

To top this off, I need to do this from another sheet (within the same
Spreadsheet)

Hope you understand.



Email me at RBotley@Gmail.com if you need me to priovide more info!


Thanks in advance


-- 
Flipside
------------------------------------------------------------------------
Flipside's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17111
View this thread: http://www.excelforum.com/showthread.php?threadid=499790

0
1/10/2006 4:15:22 PM
excel 39879 articles. 2 followers. Follow

2 Replies
403 Views

Similar Articles

[PageSpeed] 34

I think I do

SUMPRODUCT(--(Sheet1!E1:E100>0),--(Sheet1!E1:E100<2))/
=SUMPRODUCT(--(Sheet1!E1:E100>0),--(Sheet1!E1:E100<2),Sheet1!F1:F100)

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Flipside" <Flipside.21ffra_1136910001.1962@excelforum-nospam.com> wrote in
message news:Flipside.21ffra_1136910001.1962@excelforum-nospam.com...
>
> Hope you can help me!
>
> I have a column on one spreadsheet called "Platts Ports"
>
> Now, I want to analize column E. I want to be able to pick out all the
> values between 0 & 0 (>0 &<2). I then want to say the following:
>
> Where value = between 0-2 (COUNT) then add up column F where all these
> fields apply. Then divide COUNT(E) by the total of F( the sum we just
> calculated)
>
> To top this off, I need to do this from another sheet (within the same
> Spreadsheet)
>
> Hope you understand.
>
>
>
> Email me at RBotley@Gmail.com if you need me to priovide more info!
>
>
> Thanks in advance
>
>
> -- 
> Flipside
> ------------------------------------------------------------------------
> Flipside's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=17111
> View this thread: http://www.excelforum.com/showthread.php?threadid=499790
>


0
bob.phillips1 (6510)
1/10/2006 5:55:30 PM
Thanks alot man.

Great Help!!!


-- 
Flipside
------------------------------------------------------------------------
Flipside's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17111
View this thread: http://www.excelforum.com/showthread.php?threadid=499790

0
1/11/2006 11:30:50 AM
Reply:

Similar Artilces:

Ignoring 0 values in the series data
How can I get my chart to ignore values which are zero in the series data and not show these? replace zeros with =NA() Manges -- mangesh_yada ----------------------------------------------------------------------- mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1047 View this thread: http://www.excelforum.com/showthread.php?threadid=38003 You can delete the formulas that are producing zero, or replace them with =IF(formula=0,#N/A,formula) Jerry Alison wrote: > How can I get my chart to ignore values which are zero in the series data a...

Counting Unique Values within a Group
I have a report that lists students by grade and the amount of money donated by that student's family. If the parents are divorced, the student's name is listed twice, once for each parent. I want to count only the unique student record in each grade. DCount is giving me the count for the whole school regardless of what group detail i put it in. How do I count only the unique records within each grouping? Thanks in advance. I would attempt to create a totals query that groups by whatever and counts something. Add this totals query to your report's record source...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Adding records to table from a form, with pre-populated values
I'm trying to construct a form that will let me translate pre-defined "base" sentences to several languages. I have the following tables: BaseTexts: ID, Text Languages: Language Translations: BaseTextID, Language, Translation but the Translations table only has records if a translation actually exists. So I have a query that gets me all (BaseTextID, Text, Language) triplets for which a translation is needed (Text <-> BaseTextID is 1-1 mapping, but users need to see the text itself in order to translate), and I want to present a multiple-records form with 4...

Setting the value of a Master page's control's property using a property of the Master page
I have a Master page that contains a custom property, defined as follows: Public Property SelectedNavigationID() As String Get Return Me.leftnavNavigation.SelectedNavigationID End Get Set(ByVal value As String) Me.leftnavNavigation.SelectedNavigationID = value End Set End Property As you can see, this property is used to set the value of a property of one of the Master page's Controls. I set the property from the Content page as follows: Private Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit Me.Master.SelectedN...

Wrong(?) calculation result in Excel
While my calculator gives the correct result for 111,111,111 X 111,111,111 (12345678987654321, when I use Excel for the same calculation, the result I get is close, but not exact--12345678987654300. Can anyone help explain what's happening in Excel? Many Thanks, Jim Hi Go to Excel help and do a search for Specifications. In there you will see that Excel only calculates to 15 digits of precision. I'm sure others will post quoting the IEE specification that Excel works to. It's very interesting and well worth a read. -- Andy. "Jim" <jlclemen@ius.edu> wrote ...

Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2, 3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible? Thanks in advance Original Stealth, Certainly can. I created the following dataset: SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3 UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...

Calculating date difference
Hi I need to calculate the number of years of service for person(difference between Start_Date & End_Date). The result should b in terms of years, months and days (all in one single cell). Ca someone please help? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Have a look at Chip's site: http://www.cpearson.com/excel/datedif.htm Andy. "rll" <rll.xjm6a@excelforum-nospam.com> wrote in message news:rll.xjm6a@excelforum-nospam.com... > &...

calculating dates prior to a given date
Hi. I am trying create a spreadsheet that will have dates listed in one column and have another column list the date 14 days prior to the first column's dates. I'm hoping to then organize the second column ascending from the closest to the furthest date, compared to the current date. If anyone has any help they could provide it would be much appreciated. Thank You. If you have a date in A1 and you want another date in B1 which is 14 days earlier, then put this in B1: =3DA1-14 Copy this formula down, then fix the values by selecting all those cells with the formula ...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Table calculations. Season from Date in Hunting Harvest Database.
I am trying to set up a hunting harvest database. I would like to add a column to the original data table that automatically calculates the hunting season, which is based on a specific harvest timeframe. Each harvest record entry has a specific date. For example, when a record of a deer harvested on 12-3-2007 is entered; I want the database to calculate that the date occurred during the fall 2007 season. Thus, I want 2007 to be automatically entered for this record in a separate column. However, our seasons last into the new year, so if for example a record is entered of a ...

valid FirstName value
Hello everyone, Anyone know how to search for a valid FirstName in a field? eg.: Himansu Rich Anna Tom .... I don't want junk like this: AA, 0, "Himansu" 'Rich' Any assisatance will be greatly appreciated. Have a nice evening. Thanks, Himansu How about "Gert-Jan"? //Peter "Himansu" <himansu114@hotmail.com> wrote in message news:u3#lXzwZKHA.6028@TK2MSFTNGP04.phx.gbl... > Hello everyone, > > Anyone know how to search for a valid FirstName in a field? > > eg.: > Himansu > Rich ...

freight calculations
Hello: In using Sales Order Processing with GP Manufacturing, do most companies expense freight or revalue freight in cost? Also, does GP Manufacturing have the capability to calculate freight? Or, is freight manually entered into the Sales Order Processing invoice by the end user? Thanks! childofthe1980s Freight is handled a wide variety of ways by clients. Some absorbe it into the cost of sales, some list it as a separate cost of sales, some expense it. Others bill the client, pay the freight and balance the two charges. Manufacturing will not calculate real freight charges. I...

adding values of cells
I am trying to use a formula or function that will look at a cells say column A check for a condition, if true, then add the value of another cell that is on the same row to another cell. Yes I know that may be difficult to understand so let me give you an example. A B C D 1 2 dvd 25.23 3 cd 12.25 4 dvd 25.23 5 cd 12.25 6 bat 19.25 ok so what i want to do is look at column A, and look for any cells that have dvd and add the corresponding value from column D to cell B1. So in this case we can see the cell A2 and cell A4 is dvd so I want to add cells D2 and D4 and that...

Originating Values
I have been workign with GP for a while, but am, not really clear on the difference between Originating Price and Price, Originating Cost and Cost, etc. Can anyone clue me in, I haven't been able to find a good explanation. Thanks Chris I believe this has to do with Multicurrency. 'Originating' is the originating currency. If you are not using Multicurrency the Originating field will be the same as the other field. If your making any changes to the windows using Modifier be careful because these two fields are 'stacked' on top of one another. "Chris Hornun...

Misfiring calculation
I'm trying to calculate a total and pull it into a query both to show the amount and for a subsequent expression. However the result i'm getting is the product of the total times the number of occurances of its index in the table i'm totaling on. I hope that makes sence. I'm sure this is quite simple. thank you in advance Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. Information on primary keys and relationships would be a nice touch too. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bon...

Sorting Alpha-Numeric Values
I would like to sort data that have aplha numeric values. When I use the sort command, the numbers wind up getting sorted by place values instead of the actual value of the number, kind of like alphabeticaly except with numbers instead of letters. For instance, I would like to these numbers sorted like this: 17A 36B 265F 1492C 1609A Instead, Excel sorts them like this: 1492C 1609A 17A 265F 36B It appears that the suffixed alpha characters alter the way the numbers would normally be sorted. Any ideas how I can sort them more logically/numericaly? Thanks! Larry ...

Copying Specific Values From Sheet
Hi. Is there a way to copy specific rows from 1 sheet to another? For instance I have a workbook containing 4 tabs. These are labeled "raw data", "Sheet A"," Sheet B", and "Sheet C". On the "raw data" sheet there are 3 columns. One of the columns is titled "Project". In there the values are A, B, C or D (and maybe E someday). I would like to say "if the "Project" column contains and "A" copy everything on that row to "sheet A". Basically I want to take all t he information that has a proje...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

converting checkboxes to a text value
I have an existing database that employs checkboxes. I'd like to create a report that displays those boxes as "yes/no" text values, so that I can export them to an .rtf format, and have them actually show up. I'm a novice, so any help is greatly appreciated. The purpose of this is to be able to email specific pages of the report. I'm also open to other ideas for making this possible, if I'm going about this the wrong way. Thanks in advance. Charitycase wrote: > I have an existing database that employs checkboxes. I'd like to > create a report that...

Calculating averages
I have 5 cells that I am trying to average, but I only want to average those cells that contain data. Using the if statement I was able to get two cells to work, but when I tried a nested if statement I was unable to get it to work if there was only 1, 2, or 3 cells with data in them. What do I need to do to resolve this? Thanks in advance. =SUM(A1:A5)/COUNT(A1:A5) Vaya con Dios, Chuck, CABGx3 "Carl Johnson" <bjohnson@woh.rr.com> wrote in message news:hwM4b.25285$l41.7213466@twister.neo.rr.com... > I have 5 cells that I am trying to average, but I only want to average...

Conditional Max value
Hi, i have 2 columns and i'm trying to calculate the conditonal maximum from column one. These are the columns : 47 7 44 7 71 7 58 7 214 4 22 4 54 7 1 7 45 7 21 7 and i try to find a formula that gives the maximum in column one, where column 2 has value 7 in this case this would be 58 Marc Marc, Use the following array formula: =MAX(IF(B1:B10=7,A1:A10,FALSE)) Change the range references to match your data. This is an array formula, so you must press CTRL+SHIFT+ENTER rather than just ENTER when you first enter the for...

Problem inserting calculated pivot fields into Pivot Table (2007)
Working with Excel 2007 and a Pivot Table. Created a Calculated field. The field shows in the Field List, but Excel will not let me drag the Calculated field into the Pivot Table areas (other than into the Values area). What am I doing wrong? Hi A calculated item or field, can ONLY be allocated to the data area. It's position within the data area can be modified, but it cannot be moved to any other area -- Regards Roger Govier wamiller36 wrote: > Working with Excel 2007 and a Pivot Table. Created a Calculated field. The > field shows in the Field List, b...

Calculate Due Date?
I need a formula that will calculate a due date 10 days from the start date (a1)but if the date falls on a weekend or holiday I need the date to be the next working day. I will have a list of holidays in J2:J50. Thanks for any help.......... -- HTH Bob Phillips "Randy" <ranmcc@msn.com> wrote in message news:1121099149.930051.258040@o13g2000cwo.googlegroups.com... > I need a formula that will calculate a due date 10 days from the start > date (a1)but if the date falls on a weekend or holiday I need the date > to be the next working day. > > I will have a...

date and time calculations in Excel 2003
Example: H6=01/14/2010 16:45 A6=12/31/2009 12:15 I want to get the difference between January 14th 2010 at 16:45 and December 31, 2009 at 12:15. Ideally, it would be days, hours and minutes. Like this==> ddd:hh:mm The formula is: =h6-a6 Use a custom format of: dd:hh:mm Regards, Fred "CJ" <CJ@discussions.microsoft.com> wrote in message news:1D38B37F-ED29-499E-A05E-F1682FF9F153@microsoft.com... > Example: > H6=01/14/2010 16:45 > A6=12/31/2009 12:15 > > I want to get the difference between January 14th 2010 at 16:45 and > D...