soundengineer

iTrader: (16)

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

P Mack

iTrader: (6)

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)

soundengineer

iTrader: (16)

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

redmist

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.

soundengineer

iTrader: (16)

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

soundengineer

iTrader: (16)

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??

P Mack

iTrader: (6)

I will email you what i did if you want.

soundengineer

iTrader: (16)

I think I found it...

=SUMPRODUCT(($B2:$CD2>=B6-1)*($B2:$CD2<=B6+0.9)*($B3:$CD3))/SUMPRODUCT(($B2:$CD2>=B6-1)*($B2:$CD2<=B6+0.9))


got some help from somebody else

right now I'm trying to figure out how to exclude blank cellls so they dont calculate as 0's

IF you click the link in teh previous posts it takes you to my updated file...

soundengineer

iTrader: (16)

problem solved...link gone

thank you for all your help