|
Is there a way to use Formula to resolve
Is there a way or formula in the excel that could help to add the nearest
month to get the figure for Column F. The month added up can be greater than or equal to Column F. Column A to E is the month. Column F is the Qty. Column G is the column that I wish to get the month. Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans 100 100 100 100 100 300 Mar,Apr and May-3 mth to get 300 100 100 100 100 100 250 Mar,Apr and May-3 mth to have 250 100 100 100 100 100 500 Jan to May-5 month to get 500 100 100 100 100 100 100 May-1 month to get 100 100 100 100 100 100 50 May-1 month to get 50 |
Is there a way to use Formula to resolve
With only the month names (Jan, Feb..) in row 1 (ie not year)
In G2 I used =$E$1&IF(E2<F2,", "&$D$1,"")&IF(E2+D2<F2,", "&$C$1,"")&IF(E2+D2+C2<F2,", "&$B$1,"")&IF(E2+D2+C2+B2<F2,", "&$A$1,"") Another method would be to use Solver best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sky" wrote in message ... Is there a way or formula in the excel that could help to add the nearest month to get the figure for Column F. The month added up can be greater than or equal to Column F. Column A to E is the month. Column F is the Qty. Column G is the column that I wish to get the month. Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans 100 100 100 100 100 300 Mar,Apr and May-3 mth to get 300 100 100 100 100 100 250 Mar,Apr and May-3 mth to have 250 100 100 100 100 100 500 Jan to May-5 month to get 500 100 100 100 100 100 100 May-1 month to get 100 100 100 100 100 100 50 May-1 month to get 50 |
Is there a way to use Formula to resolve
Thanks Bernard.
Yes,I got the ans. Could the ans also input in the year. May I also know how to use Solver? Thanks a lot "Bernard Liengme" wrote: With only the month names (Jan, Feb..) in row 1 (ie not year) In G2 I used =$E$1&IF(E2<F2,", "&$D$1,"")&IF(E2+D2<F2,", "&$C$1,"")&IF(E2+D2+C2<F2,", "&$B$1,"")&IF(E2+D2+C2+B2<F2,", "&$A$1,"") Another method would be to use Solver best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sky" wrote in message ... Is there a way or formula in the excel that could help to add the nearest month to get the figure for Column F. The month added up can be greater than or equal to Column F. Column A to E is the month. Column F is the Qty. Column G is the column that I wish to get the month. Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans 100 100 100 100 100 300 Mar,Apr and May-3 mth to get 300 100 100 100 100 100 250 Mar,Apr and May-3 mth to have 250 100 100 100 100 100 500 Jan to May-5 month to get 500 100 100 100 100 100 100 May-1 month to get 100 100 100 100 100 100 50 May-1 month to get 50 |
Is there a way to use Formula to resolve
Send me an email (remove TRUENORTH.) and I will send a sample file
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sky" wrote in message ... Thanks Bernard. Yes,I got the ans. Could the ans also input in the year. May I also know how to use Solver? Thanks a lot "Bernard Liengme" wrote: With only the month names (Jan, Feb..) in row 1 (ie not year) In G2 I used =$E$1&IF(E2<F2,", "&$D$1,"")&IF(E2+D2<F2,", "&$C$1,"")&IF(E2+D2+C2<F2,", "&$B$1,"")&IF(E2+D2+C2+B2<F2,", "&$A$1,"") Another method would be to use Solver best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sky" wrote in message ... Is there a way or formula in the excel that could help to add the nearest month to get the figure for Column F. The month added up can be greater than or equal to Column F. Column A to E is the month. Column F is the Qty. Column G is the column that I wish to get the month. Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans 100 100 100 100 100 300 Mar,Apr and May-3 mth to get 300 100 100 100 100 100 250 Mar,Apr and May-3 mth to have 250 100 100 100 100 100 500 Jan to May-5 month to get 500 100 100 100 100 100 100 May-1 month to get 100 100 100 100 100 100 50 May-1 month to get 50 |
Is there a way to use Formula to resolve
Hi Bernard,
Did you receive my mail? Thanks a lot Kang Yong "Sky" wrote: Thanks Bernard. My email is . If there is blank in my speadsheet is there a way to capture the month. Thanks inadvance Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans 100 100 100 100 300 May Mar Feb 100 100 100 100 250 May Apr Feb 100 100 100 100 100 500 May Apr Mar Jan 100 100 100 100 100 Apr 100 100 100 50 Mar "Bernard Liengme" wrote: Send me an email (remove TRUENORTH.) and I will send a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sky" wrote in message ... Thanks Bernard. Yes,I got the ans. Could the ans also input in the year. May I also know how to use Solver? Thanks a lot "Bernard Liengme" wrote: With only the month names (Jan, Feb..) in row 1 (ie not year) In G2 I used =$E$1&IF(E2<F2,", "&$D$1,"")&IF(E2+D2<F2,", "&$C$1,"")&IF(E2+D2+C2<F2,", "&$B$1,"")&IF(E2+D2+C2+B2<F2,", "&$A$1,"") Another method would be to use Solver best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sky" wrote in message ... Is there a way or formula in the excel that could help to add the nearest month to get the figure for Column F. The month added up can be greater than or equal to Column F. Column A to E is the month. Column F is the Qty. Column G is the column that I wish to get the month. Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans 100 100 100 100 100 300 Mar,Apr and May-3 mth to get 300 100 100 100 100 100 250 Mar,Apr and May-3 mth to have 250 100 100 100 100 100 500 Jan to May-5 month to get 500 100 100 100 100 100 100 May-1 month to get 100 100 100 100 100 100 50 May-1 month to get 50 |
| All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com