I have read most all the postings and still have not found a formula that covers this situation:
I have a row with 19 cells of numeric data (some are empty and some are blank). The first 18 cells must be individually compared to cell 19 in the same row.
The cells must be added in sequence left to right until they are as great as possible but do not exceed the value of cell 19.
These operations as stated below will get the job done properly but I need to cover a wider range of cells in a row.
Please see if you are able to shorten the number of steps, it would be very much appreciated. Thank you.
First computation (sets up cell 19 "Q2" value)
=P2*C2
Second computation "R2" (Determines month the effort has extended to)
=IF(H2>=Q2, 4, IF(H2+I2>=Q2, 5, IF(H2+I2+J2>=Q2, 6, IF(H2+I2+J2+K2>=Q2, 7, IF(H2+I2+J2+K2+L2>=Q2, 8)))))
Note: The if statements are too short to cover 18 months, I could not get a formula to compute properly.
Third computation "S2" (Should add up cell data - up to the month just before the month from second computation).
=IF(R2=4, G2, IF(R2=5, G2+H2, IF(R2=6, G2+H2+I2, IF(R2=7, G2+H2+I2+J2, IF(R2=8, G2+H2+I2+J2+K2, )))))
Fourth computation "T2" (Determines balance of effort that needs to be brought into currnet month)
=Q2-S2
Fifth computation "U2" (Divides "T2" balance by number of days in current month)
=IF(R2=4, H2/30, IF(R2=5, I2/31, IF(R2=6, J2/30, IF(R2=7, K2/31, IF(R2=8,L2/31)))))
Sixth computation "V2" (Will tell which day of the month the remaining effort should be completed by)
=T2/U2
Thank you.
I have a row with 19 cells of numeric data (some are empty and some are blank). The first 18 cells must be individually compared to cell 19 in the same row.
The cells must be added in sequence left to right until they are as great as possible but do not exceed the value of cell 19.
These operations as stated below will get the job done properly but I need to cover a wider range of cells in a row.
Please see if you are able to shorten the number of steps, it would be very much appreciated. Thank you.
First computation (sets up cell 19 "Q2" value)
=P2*C2
Second computation "R2" (Determines month the effort has extended to)
=IF(H2>=Q2, 4, IF(H2+I2>=Q2, 5, IF(H2+I2+J2>=Q2, 6, IF(H2+I2+J2+K2>=Q2, 7, IF(H2+I2+J2+K2+L2>=Q2, 8)))))
Note: The if statements are too short to cover 18 months, I could not get a formula to compute properly.
Third computation "S2" (Should add up cell data - up to the month just before the month from second computation).
=IF(R2=4, G2, IF(R2=5, G2+H2, IF(R2=6, G2+H2+I2, IF(R2=7, G2+H2+I2+J2, IF(R2=8, G2+H2+I2+J2+K2, )))))
Fourth computation "T2" (Determines balance of effort that needs to be brought into currnet month)
=Q2-S2
Fifth computation "U2" (Divides "T2" balance by number of days in current month)
=IF(R2=4, H2/30, IF(R2=5, I2/31, IF(R2=6, J2/30, IF(R2=7, K2/31, IF(R2=8,L2/31)))))
Sixth computation "V2" (Will tell which day of the month the remaining effort should be completed by)
=T2/U2
Thank you.