|
sum function
Hi
I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
sum function
Maybe something like this.........
=IF(SUM(A1:Z1)=800,1&" + "&SUM(A1:Z1)-800,SUM(A1:Z1)) Vaya con Dios, Chuck, CABGx3 "cliff" wrote: Hi I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
sum function
sorry, it didn't work. It just give me a solution like "1+1400" or it just
reports "1+sum of cells) it doesn't find when the sum = 800 or greater and report a 1 then return back to zero and sum again. any other suggestions? -- cliff "CLR" wrote: Maybe something like this......... =IF(SUM(A1:Z1)=800,1&" + "&SUM(A1:Z1)-800,SUM(A1:Z1)) Vaya con Dios, Chuck, CABGx3 "cliff" wrote: Hi I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
sum function
With an actual sum in the Range A1:Z1 of 2200, the formula I suggested will
return 1 + 1400, indicating that it was 1400 over the target of 800. This is my interpretation of your PseudoFormula if(sum(a1:z1)=800,1 (and start back at zero???),"") What sort of a return are you lookinhg for when the sum of the range is 2200, and when it is less than 800? Vaya con Dios, Chuck, CABGx3 "cliff" wrote: sorry, it didn't work. It just give me a solution like "1+1400" or it just reports "1+sum of cells) it doesn't find when the sum = 800 or greater and report a 1 then return back to zero and sum again. any other suggestions? -- cliff "CLR" wrote: Maybe something like this......... =IF(SUM(A1:Z1)=800,1&" + "&SUM(A1:Z1)-800,SUM(A1:Z1)) Vaya con Dios, Chuck, CABGx3 "cliff" wrote: Hi I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
sum function
I would like it to return 1. Then I would like it to start counting again at
zero until it reaches 800 again and then return 1 again. And so on like this through the entire range. -- cliff "CLR" wrote: With an actual sum in the Range A1:Z1 of 2200, the formula I suggested will return 1 + 1400, indicating that it was 1400 over the target of 800. This is my interpretation of your PseudoFormula if(sum(a1:z1)=800,1 (and start back at zero???),"") What sort of a return are you lookinhg for when the sum of the range is 2200, and when it is less than 800? Vaya con Dios, Chuck, CABGx3 "cliff" wrote: sorry, it didn't work. It just give me a solution like "1+1400" or it just reports "1+sum of cells) it doesn't find when the sum = 800 or greater and report a 1 then return back to zero and sum again. any other suggestions? -- cliff "CLR" wrote: Maybe something like this......... =IF(SUM(A1:Z1)=800,1&" + "&SUM(A1:Z1)-800,SUM(A1:Z1)) Vaya con Dios, Chuck, CABGx3 "cliff" wrote: Hi I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
sum function
Maybe this..........
=IF(SUM(A1:Z1)<800,SUM(A1:Z1),INT(SUM(A1:Z1)/800)&" times 800 + "&SUM(A1:Z1)-(INT(SUM(A1:Z1)/800)*800)) It will return the following for a 2207 sum in the range A1:Z1 2 times 800 + 607 Vaya con Dios, Chuck, CABGx3 "cliff" wrote: I would like it to return 1. Then I would like it to start counting again at zero until it reaches 800 again and then return 1 again. And so on like this through the entire range. -- cliff "CLR" wrote: With an actual sum in the Range A1:Z1 of 2200, the formula I suggested will return 1 + 1400, indicating that it was 1400 over the target of 800. This is my interpretation of your PseudoFormula if(sum(a1:z1)=800,1 (and start back at zero???),"") What sort of a return are you lookinhg for when the sum of the range is 2200, and when it is less than 800? Vaya con Dios, Chuck, CABGx3 "cliff" wrote: sorry, it didn't work. It just give me a solution like "1+1400" or it just reports "1+sum of cells) it doesn't find when the sum = 800 or greater and report a 1 then return back to zero and sum again. any other suggestions? -- cliff "CLR" wrote: Maybe something like this......... =IF(SUM(A1:Z1)=800,1&" + "&SUM(A1:Z1)-800,SUM(A1:Z1)) Vaya con Dios, Chuck, CABGx3 "cliff" wrote: Hi I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
sum function
Or maybe even this...........
=REPT("1",INT(SUM(A1:Z1)/800)) Returns 1111 for a sum of 3207, only giving a 1 for each 800 with no remainder. Vaya con Dios, Chuck, CABGx3 "CLR" wrote: Maybe this.......... =IF(SUM(A1:Z1)<800,SUM(A1:Z1),INT(SUM(A1:Z1)/800)&" times 800 + "&SUM(A1:Z1)-(INT(SUM(A1:Z1)/800)*800)) It will return the following for a 2207 sum in the range A1:Z1 2 times 800 + 607 Vaya con Dios, Chuck, CABGx3 "cliff" wrote: I would like it to return 1. Then I would like it to start counting again at zero until it reaches 800 again and then return 1 again. And so on like this through the entire range. -- cliff "CLR" wrote: With an actual sum in the Range A1:Z1 of 2200, the formula I suggested will return 1 + 1400, indicating that it was 1400 over the target of 800. This is my interpretation of your PseudoFormula if(sum(a1:z1)=800,1 (and start back at zero???),"") What sort of a return are you lookinhg for when the sum of the range is 2200, and when it is less than 800? Vaya con Dios, Chuck, CABGx3 "cliff" wrote: sorry, it didn't work. It just give me a solution like "1+1400" or it just reports "1+sum of cells) it doesn't find when the sum = 800 or greater and report a 1 then return back to zero and sum again. any other suggestions? -- cliff "CLR" wrote: Maybe something like this......... =IF(SUM(A1:Z1)=800,1&" + "&SUM(A1:Z1)-800,SUM(A1:Z1)) Vaya con Dios, Chuck, CABGx3 "cliff" wrote: Hi I am trying to sum a set of data to a certain value and then want to return to zero and start the sum calculation again. I want to use some sort of a formula like: if(sum(a1:z1)=800,1 (and start back at zero???),"") I don't know how to get excel to return back to zero once the sum of the cells has reached 800 or higher. Any suggestions??? -- cliff |
| All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com