YAPTQ: Mixed data types in custom groups (Excel 2003)

Hello,

I'm trying to get the following accomplished in Excel 2003:
I have a column in a table that keeps track of how late a form was
turned in.  It can have an integer (for the number of days it was late)
or one of several text strings ("On time," "Cannot determine," "Outside
report dates.")

I need to be able to generate the totals for:
On time or within 7 days late,
8-15 days,
More than 15 days
Cannot determine
Outside report dates

It seems like it should be a straightforward procedure, where one can
specify a range of numbers, or a list of values, but I haven't been
able to find a way to get Excel to do this.

0
derevin (4)
11/27/2006 9:34:00 PM
excel 39879 articles. 2 followers. Follow

2 Replies
518 Views

Similar Articles

[PageSpeed] 15

Derevin

I have assumed your data is in A1:A1000

Set up a table (I've assumed J1:J5) with

<7
<15
>15
Cannot determine
Outside report dates

and set up the following formulas alongside

=COUNTIF($A$1:$A$1000,"<=7")
=SUMPRODUCT(($A$1:$A$1000>7)*($A$1:$A$1000<=15))
=COUNTIF($A$1:$A$1000,">15")
=COUNTIF($A$1:$A$1000,J4)
=COUNTIF($A$1:$A$1000,J5)

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


<derevin@gmail.com> wrote in message 
news:1164663240.226233.217390@f16g2000cwb.googlegroups.com...
> Hello,
>
> I'm trying to get the following accomplished in Excel 2003:
> I have a column in a table that keeps track of how late a form was
> turned in.  It can have an integer (for the number of days it was late)
> or one of several text strings ("On time," "Cannot determine," "Outside
> report dates.")
>
> I need to be able to generate the totals for:
> On time or within 7 days late,
> 8-15 days,
> More than 15 days
> Cannot determine
> Outside report dates
>
> It seems like it should be a straightforward procedure, where one can
> specify a range of numbers, or a list of values, but I haven't been
> able to find a way to get Excel to do this.
> 

0
11/28/2006 7:57:02 AM
Nick,

That's beautiful, especially the clever use of SUMPRODUCT.  Thank you
so much!

Nick Hodge wrote:
> Derevin
>
> I have assumed your data is in A1:A1000
>
> Set up a table (I've assumed J1:J5) with
>
> <7
> <15
> >15
> Cannot determine
> Outside report dates
>
> and set up the following formulas alongside
>
> =COUNTIF($A$1:$A$1000,"<=7")
> =SUMPRODUCT(($A$1:$A$1000>7)*($A$1:$A$1000<=15))
> =COUNTIF($A$1:$A$1000,">15")
> =COUNTIF($A$1:$A$1000,J4)
> =COUNTIF($A$1:$A$1000,J5)
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> www.nickhodge.co.uk
>
>
> <derevin@gmail.com> wrote in message
> news:1164663240.226233.217390@f16g2000cwb.googlegroups.com...
> > Hello,
> >
> > I'm trying to get the following accomplished in Excel 2003:
> > I have a column in a table that keeps track of how late a form was
> > turned in.  It can have an integer (for the number of days it was late)
> > or one of several text strings ("On time," "Cannot determine," "Outside
> > report dates.")
> >
> > I need to be able to generate the totals for:
> > On time or within 7 days late,
> > 8-15 days,
> > More than 15 days
> > Cannot determine
> > Outside report dates
> >
> > It seems like it should be a straightforward procedure, where one can
> > specify a range of numbers, or a list of values, but I haven't been
> > able to find a way to get Excel to do this.
> >

0
derevin (4)
12/1/2006 9:14:00 PM
Reply:

Similar Artilces:

LOOKUP CONTROL ERROR: Could Not Determine TypeName for Type=2020
Hello NG. I get an error message (LOOKUP CONTROL ERROR: Could Not Determine TypeName for Type=2020) when I assign a queue to an activity by using the form assistant. After clicking OK everything works fine but how can I switch off the error message. Thanks Hi! Is this in relation to any custom entities, the creation/removal of any of this? Have you tried to resave and republish the form of the entity? Gustaf Westerlund http://www.gustafwesterlund.blogspot.com/ "Ulf Unger" wrote: > Hello NG. > > I get an error message (LOOKUP CONTROL ERROR: Could Not Determine ...

Column BaR Chart and Data Labels
My end user want to show the datalables (+ve or _ve) on same side. I can;t find a specific option. In any case it only appears next to axis (TOP, Centre or Bottom) but all depends on +Ve and -ve numbers. Our requirement is that even number is +ve, it will stil show the Datalables on the same side as -ve numbers. I have option to remove the data lables and user Axis Labels :-). Any idea -- Malik Hi, One of many options is to seperate your =ve and -ve data into two series using =IF(B1>0,B1,NA()) and =IF(B1<0,B1,NA()) and plot both series formatted the same. Then if you ad...

sum weekly data into monthly
I have data in weekly buckets which my column names are the in the following format - SKU, 04262010, 05032010, 05102010 etc. where 04262010 is the week ending 4/26/2010 I have a second table that assoicates the column name to a fiscal month in this format - DATE Fiscal Month 20100426 6 20100503 6 20100510 6 20100517 6 I want to sum by sku for fiscal month. Drunkle - This would be much easier if your table structure was correct. Your table should have columns for SKU, WeekEndDate, and Amount, and the WeekEndDate should be a date field. Since column names are ...

obtaining data from a list
I need to get some values from a list but I don't want to sort or filte the list. I want a user in input some values and I want to obtain value in the list based on what was entered. Then I want to use tha value in other calculations. Also the list is formatted like this: material thk lbs/sqft cs 3/16 7.66 cs 1/4 10.2 cs 5/16 12.8 cs 3/8 15.3 cs 7/16 17.9 ss 14 ga 3.15 ss 10 ga 5.67 ss 3/16 8.58 and I want to get the lbs/sqft based on the first 2 columns. So the input would be cs and 5/16 I need to get 12.8 ...

Automatically highlight certain data.
I have a spreadsheet with a schedule of duties that are done every Sunday. Different people are assigned each week for each duty. For instance: Sunday, May 2 Greeters Mr. x Ushers Mrs. y Counters Ms. z It's a vey long list and a very large spreadsheet (covers several years of Sundays) I'd like to be able to highlight/change letter color for the Sunday list that has the info for the upcoming Sunday. I've been trying to figure it with 'Conditional Formatting' but there must be a calculation, or something, to figure out h...

Group Items on Report but do Not Sort the Group alphabetically
I have a table called "category" with a list of dropdown items in it. The table only has an autonumber key and the list of items in it. The items are listed in a specific order in the table and are not sorted alphbetically in the table nor in the query for the table. The items are listed in the table as a dropdown in this order and the query also shows the items in this order: Administration Human Resources Events Periodicals I want to group the data in my report based on the "category" listing as shown in both the table and the query. However, as soon as "group&...

In 3 active sheets in wkbk, determine& display the # of sheets that have data
I am wking with potentially 3 active sheets in a wkbk i.e. anyone of them or all could have data or text entered in them at any time. This is an ever changing scenario as data is entered or deleted i.e. a sheet is filled and the next sheet receives the new data etc. Since I am able or only want to see one sheet at a time, it is necessary to see on the displayed sheet how many of the 3 sheets have data on them at anyone time and to display that # of sheets numerically on all 3 sheets as I move thru them. I guess most simply stated I would like to know when I am in sheet #1 whether 1sheet is...

Why won't my newly entered data sort with old data ?
I have an excel spread sheet that the data in the column sorts newly entered or changed data seperatly from old data. there is a good chance that some of the data, if numeric, either new or old, it text even though it looks like numbers. If you do a quick sum() on some of the numbers, if it says 0 it is probably text. a helper column with =value(trim(A1)) then copy and paste special values will convert to numeric. There are other ways to convert text numbers to numeric, depending on what is really there. If it is text, there may be leading spaces. Check the trim() function. "...

Test linked form for data
Hi Groupies How can I test to see if a linked form, that might not be visible, has data? My main form has a yes/no box that enables a button when it is set to yes. The button opens a linked form. When the user moves to the next record, I would like the main forms BeforeUpdate event to make sure that the yes/no box is not set at No while there is data in the linked form for that record. I have tried something like: If Me.Form.frmVehicles.RecordsetClone.RecordCount > 0 And Me.CompanyVehicle = False Then Cancel = True MsgBox "Please check vehicles", vbOKOnly Me.f...

Excel Finding instances of a value in defined range
Can some one help me with a small excel problem.. I have a excel sheet in which I have.... wait... why dont you check th attached file and then tell me how do I find out the repeated instance of a check.. For example , I want column H to say - "Instance Correct" if a chec no. X has been found 2 times in the defined range A1-F7 , and if th amount mentioned under deposit column of Bank is same as amount mentio in Deposit column of company.. If the check number is found 2 times bu the amount is different then column H should say - "Instance Wrong", i the check number is foun...

Mixing data from 2 different tables
OK, this is trickier than it sounds. Table A has a list of situations and a step-by-step list of categories. Example: Situation Order Ruleset User Clicks OK 1 Ruleset A User Clicks OK 2 Ruleset B User Clicks OK 3 Ruleset D User Clicks OK 4 Ruleset X User Clicks OK 5 Ruleset A <== That's right, same ruleset can show up twice in list Only showing one "Situation", but there would be many. Then, there is another table of Rules in each Ruleset. Example: Ruleset Rule ...

How do I export the results of a table into excel?
Hi, I have created a table with a filter and group layout that I would like to export the values into excel. However, with the export feature, I am only able to select a filter and not a table with grouping. I can cut/paste, but there must be a better way. Thanks so much Easiest would be to just recreate the grouping in Excel. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, > I have created a table with a filter and group layout that I would > like to > export the values into excel. However, with the export feature, I am > only > ...

Pdf File To Excel #2
How To Convert Pdf File Into Excel File. I Am Having Pdf File Which Are Created From Graphs & Want To Covert I Excel Pls Suggest Me Any Link Or Addins Thanks In Advance -- NITESH ----------------------------------------------------------------------- NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1759 View this thread: http://www.excelforum.com/showthread.php?threadid=49602 You need to have one of the following, Adobe Acrobat Exchange (5.0) Acrobat Professional (6.0) or Acrobat 7.0. There are some other vendor who make programs which will...

Splitting data in a cell #2
Hi, I have a spreadsheet which has data entered into a single cell an split onto seperate lines - they ar eaddresses. I need to get each lin out of the cell and into a seperate cell. How can i do it? Thank -- Number_ ----------------------------------------------------------------------- Number_8's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=659 View this thread: http://www.excelforum.com/showthread.php?threadid=27201 Hi try using 'Data - Text to columns' for this "Number_8" wrote: > > Hi, > > I have a spreadsheet which h...

how can I stop sub total rows appearing in pivot tables in excel
I am working in excel and I need to prevent sub total rows appearing in my pivot tables. Does any know if this is possible and if so how? Also, I am working in Access and I need to be able to have multiple columns headers (grouped) similar to what you can do in Excel, any ideas? Thanks for your help. Lee If you rightclick on the button like header in your pivottable, you can select "field settings". there's a "none" option for subtotals. If you don't get an answer for your access question, you may want to try an Access newsgroup. Lee wrote: > > I am wor...

Can I pull Data from a list by store # and quantity
Can I pull store #'s to another spreadsheet along with quantity of items? I have a Distribution spreadsheet with a list of 100 store #'s. I fill out the quantities of the items they each will recieve. The description transfers to another spreadsheet that is used to make tags for the product. sometimes, certain stores won't get anything and others might get 2. Is there a way for the store numbers to carry over to the label page while and create tags for the stores quantity? Hard to explain....hope someone can understand. Thanks, Sarah ...

what is mixed cell referencing
what is mixed cell referencing and what is the difference between mixed, absolute and relative? Yathin, Mixed cell referencing is when you have an absolute row and relative column reference, or vice-versa. This really comes into play when you copy a cell's formula. For instance, assume that the cell C1 has the formula =A1+B1. If you copy C1 to C2, you will get the formula =A2+B2. This is fully relative referencing. However, if you use an absolute reference, you fix that part of the formula. For example, in C1 use =A$1+B$1, copy that to C2 and you get the same formula, namely =A$1+B$1...

Transfer of data #2
Microsoft Money 2003 on a Windows 98 machine. Made back up. Can't seem to get the data over to a newer Windows XP machine. Installed original software from disk just fine. Tried to import the data into it from the backup disk. Tried to restore from the backup disk. Info fine on the floppy, copied fine to the hard drive but it does not seem to want to take it. Is there a better way to transfer data from old progam on older machine to same program on a newer machine? Restore is the right choice for a backup. A better choice is to move the original .MNY file and skip the whole...

Linked Excel Table in Multi-user environment
Hello - I just deployed an application with a frontend that links to an Excel sheet being used as a table. It tested fine with only one user (me) accessing it at a time, but now in the multi-user environment, we're getting a message stating that the Excel table cannot be accessed because another user (the first user in presumably) has exclusive access to it. Thank you in advance for your help with this problem. Jake ...

How to reset HQ data files
I have an RMS system with HQ and two stores. They have been used extensively in testin, training, etc. We need to purge all this test data prior to go-live of the system. This would include transaction data, journal, historical financial data, orders, purchase orders, etc. Of course we wish to retain setup data like item records, HQ users, departments and categories, etc. I can find no reference to this information anywhere. Can you direct me to the appropriate source? Thanks in advance. ...

Excel 2003 save as earlier version
How can I save my Excel 2003 order form in an earlier version? Many of our customers can't open/use it as they don't have Excel 2003. It prompts them to load Microsoft Component and most times, they can't download it. -- Thank you, Gayle I think you are confusing your versions of Excel. 2003 uses the same file format as all versions since 97. In 2007, the file format was changed. To accommodate this, you have two options: -- upgrade your 2003 versions to be able to read these files (upgrade available on the Microsoft website) -- in 2007, save the file in 97-200...

Is this data stored somewhere?
I have developed a Smartlist w/SLB utilizing the SOP30300 table and am trying to either bring in or calculate the total qty (all are UofM 'Case') per invoice. Is this data stored anywhere or is there a transact sql statement I can use in SLB? MES, The SOP30300 table has a field called QTYBSUOM or something like that. You can use it to hook to the IV40201 and IV40202 tables, which is where the UOM schedules are stored. You should be able to make any conversion from there. "MES" wrote: > I have developed a Smartlist w/SLB utilizing the SOP30300 table and am trying ...

Pocket PC 2003 version of Microsoft Money 2002
I have Microsoft Money 2002 installed on my computer and I want to use it on my Pocket PC (Viewsonic V37 - Windows Mobile 2003). Does anybody know where I can download microsoft money 2002 for pocket PC? Microsoft's download page <http://www.microsoft.com/windowsmobile/downloads/money.mspx> only has money 2004 for pocket pc and 2003 for windows mobile 2002. When I try to install money 2004 for pocket pc It says that money is not installed on my PC. I have the same issue! I've been looking all over the place for MSMoney 2002 for PPC (mine's T-Mobile). If there is a MS Exp...

Resetting Data By Week Or Month
Ok ...what I Have Is A Workbook That Contains 7 Sheets ...eached Named Diffrent Day Of The Week...mon-sun...what I Need It To Do...is O Sunday...roll Some Of The Info Over To Monday...and Reset Some Of Th Info To Zero...keeping My Monthly Totals But Resetting The Weekl Total...then On The First Of The Month...even If Its In The Middle O The Week...reset The Monthly...but Keep Adding The Weeekly...so I Thin I Need To Know About Linking The Sheets To Computer Cloc Somehow...thank You...much Appreciation. -- n8rboy ----------------------------------------------------------------------- n8rboy1...

Outlook 2003 won't print message headers
This has just started happening in the last few weeks to other people in my office and now to me. I haven't been able to trace the problem back to any specific update (at least not yet) mainly due to a lack of input from the people this has happened to. The problem is while printing emails from Outlook 2003 I can print the body of the message, but the message header (email addresses to, from, cc, etc.. and subject) won't print. We are all using word as the email editor and I originally thought that a change in the word normal template may have been the cause, but reverting to a norm...