Is there a way to subtract a column that equals criteria of another column?
example:
A B C
1 0
1 1
1 2
1 3
1 4
1 4
2 5
2 6
3 6
1 7
1 8
So What i'm asking is Say A is a catagory and B is Second column C is the total time in that catagory. I want, if the criteria in A is 1, then subtract the column of the highest point in that first section from the lowest point. Then display that in C to show me the total time it was in Catagory 1. Then just have it continue on. thanks.
Last edited by TheGame0135; 07-08-2010 at 10:55 AM.
Do you mean?
=MAX(IF(A$1:A$10,A1,B$1:B$10))-MIN(IF(A$1:A$10,A1,B$1:B$10))
Adjust ranges to suit (keep as defined ranges, not whole columns)
Then Confirm with CTRL+SHIFT+ENTER and not just ENTER and copy down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
no, your answer just gave me zeros...
what I'm asking for is this...if I took the same data my answers would be.
A B C
1 0
1 1
1 2
1 3
1 4
1 4 4
2 5
2 6 1
3 6 1
1 7
1 8 1
because the first set of 1's is 4-0, the first set of two's is 6-5, the first set of 3 is 6-0 (which i wanted to be counted as 1), and the second set of 1's is counted as 8-7. does this make sense? I need a automated way of doing this because I will be counting through thousands of points. So it won't always have the same numbers or ranges.
Make the numbers start in Row 2.. then in C2:
confirmed with CTRL+SHIFT+ENTER and copied down
Please Login or Register to view this content.
Note: For the single value of 3 in column A, I get 0 since both max and min are 6.... ???
i've been trying your code, and i keep getting div/o errors...
ooops i forgot to hit CRTL+SHIFT+ ENTER, it worked perfectly thank you so much! now i get to put it into Matlab, oh the thrill of that program, why can't eveyrthing work in excel lol!
although this does work we still have that zero issue, is there a way I can put in there somewhere if=0 make the answer 1?
Maybe:
CSE confirmed againPlease Login or Register to view this content.
As long as you don't expect negatives... then maybe:
CSE confirmed againPlease Login or Register to view this content.
there definitely won't be any negatives i'll try this
post #9 didn't not work, i'm trying 8 now
post 8 worked beautifully thank you so much!
Not even sure there is a difference... I was just trying to edit my quote to let you know about the negatives... I didn't change the formula (I don't think).. oh well, in the end we got you there
Please mark thread as Solved.
From PM:
Try:Originally Posted by TheGame0135
confirmed with CSEPlease Login or Register to view this content.
quite remarkable, thank you so much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks