Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
Hi
I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
Since you show your data starting in Row 1, put this formula in J1...
=IF(H1=50,H1,"") then put this formula in J2 and copy it down... =IF(SUM($H$1:H2)-SUM($J$1:J1)=50,SUM($H$1:H2)-SUM($J$1:J1),"") -- Rick (MVP - Excel) "Ross" wrote in message ... Hi I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
Hi Rick-Thanks for responding so quickly. My data actually begins in H10, so
I updated the formula accordingly. It worked until the figure after the first input in J. At that point, it started adding the amounts and inputting them in J instead of starting over. I'm wondering if I did something wrong when I updated the formulas maybe?? Here are the formulas I used in the J column: =If(H10=50,H10,"") =If(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10,"") -- smither fan "Rick Rothstein" wrote: Since you show your data starting in Row 1, put this formula in J1... =IF(H1=50,H1,"") then put this formula in J2 and copy it down... =IF(SUM($H$1:H2)-SUM($J$1:J1)=50,SUM($H$1:H2)-SUM($J$1:J1),"") -- Rick (MVP - Excel) "Ross" wrote in message ... Hi I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
The formula you posted is missing a closing ) after the last SUM function.
However, the missing ) won't cause what you describe. When adding the needed ) the formula works correctly. -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Rick-Thanks for responding so quickly. My data actually begins in H10, so I updated the formula accordingly. It worked until the figure after the first input in J. At that point, it started adding the amounts and inputting them in J instead of starting over. I'm wondering if I did something wrong when I updated the formulas maybe?? Here are the formulas I used in the J column: =If(H10=50,H10,"") =If(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10,"") -- smither fan "Rick Rothstein" wrote: Since you show your data starting in Row 1, put this formula in J1... =IF(H1=50,H1,"") then put this formula in J2 and copy it down... =IF(SUM($H$1:H2)-SUM($J$1:J1)=50,SUM($H$1:H2)-SUM($J$1:J1),"") -- Rick (MVP - Excel) "Ross" wrote in message ... Hi I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
You had a minor syntax error in your 2nd formula (you left out a closing
parenthesis on the last SUM function); but, other than that, I think your formula is correct. Try doing it again and just copy/paste these into J10 and J11... =IF(H10=50,H10,"") =IF(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10),"") As far as I can tell, the above two functions should work (at least they seem to work on my system). If they don't work for you, can you post the actual data you have which makes them fail so that I can test them out here on my system? Oh, and when you ask questions in the future, do not simplify them for us... post them using your actual conditions... as you should now see, solutions can be very dependent on these actual conditions. -- Rick (MVP - Excel) "Ross" wrote in message ... Hi Rick-Thanks for responding so quickly. My data actually begins in H10, so I updated the formula accordingly. It worked until the figure after the first input in J. At that point, it started adding the amounts and inputting them in J instead of starting over. I'm wondering if I did something wrong when I updated the formulas maybe?? Here are the formulas I used in the J column: =If(H10=50,H10,"") =If(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10,"") -- smither fan "Rick Rothstein" wrote: Since you show your data starting in Row 1, put this formula in J1... =IF(H1=50,H1,"") then put this formula in J2 and copy it down... =IF(SUM($H$1:H2)-SUM($J$1:J1)=50,SUM($H$1:H2)-SUM($J$1:J1),"") -- Rick (MVP - Excel) "Ross" wrote in message ... Hi I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
Hi
You're right! I did forget it. My system doesn't allow me to do a copy/paste from Excel into this window (which is strange since I was able to do the copy/paste from here into my spreadsheet) so I had to manually type it and I missed that. Thanks smither fan "T. Valko" wrote: The formula you posted is missing a closing ) after the last SUM function. However, the missing ) won't cause what you describe. When adding the needed ) the formula works correctly. -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Rick-Thanks for responding so quickly. My data actually begins in H10, so I updated the formula accordingly. It worked until the figure after the first input in J. At that point, it started adding the amounts and inputting them in J instead of starting over. I'm wondering if I did something wrong when I updated the formulas maybe?? Here are the formulas I used in the J column: =If(H10=50,H10,"") =If(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10,"") -- smither fan "Rick Rothstein" wrote: Since you show your data starting in Row 1, put this formula in J1... =IF(H1=50,H1,"") then put this formula in J2 and copy it down... =IF(SUM($H$1:H2)-SUM($J$1:J1)=50,SUM($H$1:H2)-SUM($J$1:J1),"") -- Rick (MVP - Excel) "Ross" wrote in message ... Hi I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
Hi Rick
You're right, of course. The circumstances do make a difference. Also, the closing parenthesis problem came about because nothing happens when I try to do a copy/paste from my spreadsheet into this window. I had to do a manual and I messed it up. As I am able to copy/paste from this window into a ss, I tried your solution again this morning. I got the same error, so I tried it again into a new workbook, and it worked great! Obviously, a problem somewhere in the original spreadsheet. Thanks again. This is a solution for several spreadsheets I keep. -- smither fan "Rick Rothstein" wrote: You had a minor syntax error in your 2nd formula (you left out a closing parenthesis on the last SUM function); but, other than that, I think your formula is correct. Try doing it again and just copy/paste these into J10 and J11... =IF(H10=50,H10,"") =IF(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10),"") As far as I can tell, the above two functions should work (at least they seem to work on my system). If they don't work for you, can you post the actual data you have which makes them fail so that I can test them out here on my system? Oh, and when you ask questions in the future, do not simplify them for us... post them using your actual conditions... as you should now see, solutions can be very dependent on these actual conditions. -- Rick (MVP - Excel) "Ross" wrote in message ... Hi Rick-Thanks for responding so quickly. My data actually begins in H10, so I updated the formula accordingly. It worked until the figure after the first input in J. At that point, it started adding the amounts and inputting them in J instead of starting over. I'm wondering if I did something wrong when I updated the formulas maybe?? Here are the formulas I used in the J column: =If(H10=50,H10,"") =If(SUM($H$10:H11)-SUM($J$10:J10)=50,SUM($H$10:H11)-SUM($J$10:J10,"") -- smither fan "Rick Rothstein" wrote: Since you show your data starting in Row 1, put this formula in J1... =IF(H1=50,H1,"") then put this formula in J2 and copy it down... =IF(SUM($H$1:H2)-SUM($J$1:J1)=50,SUM($H$1:H2)-SUM($J$1:J1),"") -- Rick (MVP - Excel) "Ross" wrote in message ... Hi I have a ss that I need help with. Col H is a hard input. The formula for column J is what I need. The deciding factor is an accumulation of $50.00 or more in Col H, at which time it would show that amount in Col J and start over from that point until it accumulates another $50 or more. I hope this makes sense. Col H Col J -.71 4.19 23.55 24.60 51.63 5.00 90.00 95.00 Thanks for your help... -- smither fan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|