Excel MAth Gurus..I need help

Reply Subscribe

Thread Tools

Search this Thread

Oct 16, 2005 | 09:58 PM

soundengineer

Thread Starter

8 Second Club

iTrader: (16)

Joined: Jul 2003

Posts: 4,651

Likes: 17

From: Chicago IL

Excel MAth Gurus..I need help

Ok ...so look at the 2 atatchments(pics removed...no longer needed) if you want to see what I am lookig at so you understand what I mean...

table 1 states Airflow mode...
incriments are
0,1,3,7.5,9,10,11,12,13.5,14,16

I am logging data and sorting it by the g/sec in that table....0,4,8,12,16,20,24,28,32,36,40...etc

I need to sort that data by the modes in table 2...but modes in table 1 are not matching table 2...
meaning I need to sort that data by modes 0,2,4,6,8,10,12,14,16
but in the table1 its 0,1,3,7.5,9,10,11,12,13.5,14,16
so how do I sort 0,1,3,7.5,9,10,11,12,13.5,14,16 into 0,2,4,6,8,10,12,14,16 groupings

and by the way I need it to sort this depending on what the airflow mode table is..and its different on almost every vehicle
so I'm going to be making part of teh excel file be a "put in your airflow mode #'s in these boxes"

thanks..If I confused you..sorry...but I can explain more over the phone if you can really help me

Last edited by soundengineer; Oct 17, 2005 at 06:47 PM.

Oct 16, 2005 | 10:20 PM

P Mack

TECH Addict

iTrader: (6)

Joined: Jan 2003

Posts: 2,382

Likes: 2

From: Phoenix

Try this. If you have the values 0, 1, 3, 7.5, whatever in column A and you want them to be sorted into the right groups in column b the command for column b would be:

= A1 - mod(A1,2)

Oct 16, 2005 | 11:44 PM

soundengineer

Thread Starter

8 Second Club

iTrader: (16)

Joined: Jul 2003

Posts: 4,651

Likes: 17

From: Chicago IL

not sure If thats what I need, doesnt make sense to me...
heres a link to the way I need it to work..heres the way I will be having data....cause thats the table I have set up in HPT
*link removed...no longer needed*

updated Excel sheet with new formula...

*edited out section cause it no longer pertains*

If you can tell me what to do to make that happen...I'll give you a cookie...LOL

Last edited by soundengineer; Oct 17, 2005 at 06:46 PM.

Oct 17, 2005 | 09:01 AM

redmist

TECH Regular

Joined: Feb 2004

Posts: 438

Likes: 0

For example do you want groupings 1 and 3 to be allocated to group 2 on the other table or to you want them to fall out as "unmatched"?
You could create blank columns on table two that don't have values corresponding to table one so that each sheet will have the same "keys" even though there may not be any corresping data.
The other thing you can do if use the VLOOKUP of SUMIF functions to compare the two common values in the tables and spit out the value in another column of one of the spreadsheets that you want. If the "key" doesn't exist in the other table the formula can indicate that as "False".
ie. If value in sheet 1 col 1 equals the value in sheet 2 column 1 then give me the value in sheet 2 column 4.

Oct 17, 2005 | 11:36 AM

soundengineer

Thread Starter

8 Second Club

iTrader: (16)

Joined: Jul 2003

Posts: 4,651

Likes: 17

From: Chicago IL

well.. I guess I need it to sort the values...
anyting 0->1 in box 0
anything 1.1->3 in box 2
anything 3.1->5 in box 4

but I need it to look at line 1 for the sorting and use the data from line 2

Last edited by soundengineer; Oct 17, 2005 at 12:39 PM.

Oct 17, 2005 | 12:44 PM

soundengineer

Thread Starter

8 Second Club

iTrader: (16)

Joined: Jul 2003

Posts: 4,651

Likes: 17

From: Chicago IL

well..I think I'm making progress..
decided to sort by samples and then by sum of samples...then do sum of samples divide by # of samples giving me an average...
but I cant seem to figure out how to include decimals..

I tell it this
=SUM((B1:CD1>=F5-1)*(B1:CD1<F5+0.9))
and on 8(whic 8-1 is 7 and 8+0.9 is 8.9
it doesnt seem to want to include 7.5 in there...
how do I tell it that decimals are ok??