Im trying to write an If statement somthing like this (this dosent work atm)
=(and(isnumber(m2), isnumber($l$2), (m2>$e$5), M2-$l$2, $e$5)
Im trying to have M2-L2 and output the difference untill m2 and e5 are equal. Any help would be appreciated. Im a novice when it comes to excel.
Last edited by William123; 10-12-2011 at 02:42 PM.
How about just?
=IF(AND(M2<>"",L2<>""),IF(M2>$E$5,M2-L2,$E$5),"")
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.
Ill give that a try. Thanks!
Had to make a mod to the above post. I need L2 as an absolute as well.
Then:
=IF(AND(M2<>"",$L$2<>""),IF(M2>$E$5,M2-$L$2,$E$5),"")
Last edited by NBVC; 10-13-2011 at 08:33 AM.
Thanks for your help!
That worked great. But i need to add to it for the table im working on. Not sure how but heres what im trying to do. (not sure but the absolutes might have to change)
=If (And(J2>1,perform our formula), E2) When M column = E column, go to the next number in the E column and if greater then the last add the corresponding (relative) number from the L column untill equal, if less then the last subtract the (relative) number from the L column until equal. Unitl M and E columns are equal again and then repeat the process for the rest.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
OK here it is. This is a sample from a much bigger table. Im looking to figure out the Distances/Sec in the M column.
The value for J11 is off. There will never be a negative in this field
Now can you re-explain from the start what we are trying to accomplish? Is column M showing the desired results?
Yes, I am taking the time (C) between recorded distances (H) to figure out the average distance travelled per second (L). Taking the number from the starting distance (H2) and either adding or subtracting the L column from it (Depending if the preceeding H value is larger or smaller) until they are equal. The goal is to track a targets distance per second from the starting point in the M column.
Last edited by William123; 10-13-2011 at 08:49 AM.
Can you give a sample of expected numbers in column M based on the current sample?
The expected are now in Column N. I added a few more distances to help with the calculation.
Each Distance in Column N represents a position 1 second after the last.
I updated the spreadsheet with better headers and re-arranged things. I hope this is a bit better.
Ok, it's not as easy as it looks, unless I missed something obvious.
So I had to add a few columns (as helpers) over to the right.. each has a comment at top to describe why the column is needed....
you will have to adjust ranges to suit.
Hopefully you can make sense of it.
Note that some of those formula are Array Formulas, which means they were confirmed with CTRL+SHIFT+ENTER instead of just ENTER before being copied down. You will see which they are as they have { } brackets around them.
then column J has new formula:
In J3:
=IF(R3="","",IF(AND(J2<>"",$I$2),IF(J2>R3,J2-Q3,J2+Q3),""))
copied down.
Last edited by NBVC; 10-13-2011 at 11:56 AM.
Much appreciated! you are an excel God. I been working on this for a week without much success. Im still having some some issues making it work in the origional sheet with the array formulas. But ill keep working at it.
The array forumlas seem to be locking the system up. I think I may have to many cells included. Any ideas how to fix that, besides removing cells?
I couldn't think of other ways other than what I showed. Are you using ranges that are larger than really necessary to capture all the data?
Just barely, the problem with it is there are 30000 cells in each column. Some of which are blanks as shown in the example.
Ok try the attached version... I removed all the array formulas except the one in column R.... I added another helper to index the matches on non blanks, in column M now. Everything else shifted one column over.
This should be considerably faster.
Last edited by NBVC; 10-13-2011 at 02:32 PM.
Note:
There was a slight adjustment I made to column M in the last attachment.... the previous attachment was off a bit....
I appreciate the help NBVC. Still having some issues with freeze ups. I think its just the volume of the data. It did however take a few more of the calculations after the array formulas were removed.
Did you see my last attachment in Post # 22? It shows that there have been no views currently.
Yes, for some reason after I save it the redirect isnt working back to the site. Its prob the restricitons on my work PC.
Okay, one more attempt.
Now there are no array formulas.
I inserted yet another helper column to do what the array formula was doing... that's column R now....
I moved all my data onto a quicker laptop. There is no longer a hangup problem. But I do have a few issues. In the N column my first 12 cells show up as they are suppose to with the time changes but after there are spaces that appear in the list. I added a few more cells to a few of columns in the spreadsheet to show you where the issues arise.
Not sure of exactly what you mean....
See attached, is it fixed?
If not, be specific about what is wrong and why.
No, in the newest attachment i just added some more data (times and distances). Actually upon further review I think the first problem lies in column H. (calculating the difference in distance)
The problem is that there is inconsistant spacing between the measured distances. (D Column). Thus making my formula wrong. (for the H column)
That'll require another array formula:
=IF(E2="","",ABS(MATCH(TRUE,INDEX(E3:E$49<>"",0),0)-E2))
copied down adjust as needed
Maybe Im missing somthing here or the forumla needs adjustment. Im looking to find the total change in distance (For the H column) between each measued distance.(in the E column) With the formula above for cell H2 when entered in H2 im getting 195.5. Where I would want to see 10.9 as the result.
Sorry, forgot to Index....
try:
=IF(E2="","",ABS(INDEX(E3:E$49,MATCH(TRUE,INDEX(E3:E$49<>"",0),0))-E2))
Thanks! that fixed the issue with Column H. Now im having the same issue with Coulmn I, with the inconsistant spacing my forumla is wrong.
Column I is suppose to be the avg change in dist/sec. (column H/column G).
Are E, F and G correct? If so, then what is the logic supposed to be for I? Right now, your formula in I is dependent on both G and H of the same row being populated and according to that logic, the formula in I is giving the correct results.
Column I is correct untill cell I36 where there is a change in the spacing between columns G & H.
E, F and G are all correct, yes.
As I said, then this part AND(ISNUMBER(G35),ISNUMBER(H35)) of the formula in I is not really correct then. Right?
What should it be looking at... i.e. what are the expected results and in what cells?
Thats right its not correct, In this cell it should be looking at the change in Dist (columnH) / in that time (column G). To give you the average change in time per second (Column I). The problem with everything seems to be where distance (Column E) starts being measured on a different time position in (Column C.) (Its the same time just puts it in another row in (column H) Thus Columns H and G no longer have the same spacing. Thats the way my data is layed out. (each of those intervals of dist measured is from a different point at the same time. But only one is ever used at any given time) I added another Coulmn (J) for the expected Column I results.
Last edited by William123; 10-18-2011 at 11:33 AM.
Try:
=IF(G2="","",INDEX(H2:H$49,MATCH(TRUE,INDEX(H2:H$49<>"",0),0))/G2)
Works perfectly! thanks. Sorry to be a bother, this spreadsheet is just really complicated for my limited excel experience.
No problem,
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Acutally there are still a few issues. I tryed to extend the formulas in Columns O, P, Q, R, S & T but they dont seem to be working past what was already being diplayed. Is there somthing I should be changing in the existing formulas to make them work with our new data?
As far as I can see only the formula in column P needs to be adjusted...
and confirmed with CTRL+SHIFT+ENTER and copied down.
Please Login or Register to view this content.
I tryed pasting that in and it dosent seem to be working. Ill attach an updated copy of the spreadsheet. Its easier to see then have me try to explain whats happening. Im trying to get the additional columns to calculate as far as row 49 with the rest.
The formula in P3 should be:
copied downPlease Login or Register to view this content.
and in R3 should be:
CSE confirmed and copied downPlease Login or Register to view this content.
There still seems to be an issue with addressing or somthing not all the data for N (should have 15 rows of data, refer to G),O (should have 15 rows of data, refer to I),P(missing 190.4 and 189.7, refer to E), Q(after 17 are wrong, should count to 21, refer to G) and R is missing data. If you think it would be easiest I can add columns with expected data for these Columns?
Here is an attachment with the expected values.
I don't know what to say anymore... conditions are changing, samples are not consistent to what we started with...
I have tried one last time to satisfy the requirements.... see attached.
I think you have it, with that one. It change because I was just adding in rows for refernce with the expected values.
I appreciate all your help NBVC. You are a Excel genius. This is a very difficult task. Once I paste it in my bigger spreadsheet ill mark the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks