vsuripeddi
asked on
Auto adjust a formula in excel
I have an excel file with 3 columns. They are date, units sold and the sum of units sold.
Column E has Sale method. Cell "E2" would specify the sale method.
There are 2 kinds of Sale methods. One is "Regular" and the other is "Deferred".
Regular Sale method is as follows.
The units are always sold in either June or December. All the months from Jan to Jun are sold in June and from July to December are sold in December. This keeps repeating for every year.
I was able to create a sheet for the Regular sale in the "Regular" worksheet.
Deferred sale is as follows.
There is a 6 months lag in the sale period. The units from Jan to June are sold in December and from July to December are sold in Jan. My required output is shown in "Deferred" Sheet.
I would like to create a formula in a single sheet and a single column ( Column C), which can handle both Deferred or Regular and can give me the output. The user has to refer to cell "E2" in the formula in column C.
How can I do this? Please suggest. The file is attached.
Book1.xlsx
Column E has Sale method. Cell "E2" would specify the sale method.
There are 2 kinds of Sale methods. One is "Regular" and the other is "Deferred".
Regular Sale method is as follows.
The units are always sold in either June or December. All the months from Jan to Jun are sold in June and from July to December are sold in December. This keeps repeating for every year.
I was able to create a sheet for the Regular sale in the "Regular" worksheet.
Deferred sale is as follows.
There is a 6 months lag in the sale period. The units from Jan to June are sold in December and from July to December are sold in Jan. My required output is shown in "Deferred" Sheet.
I would like to create a formula in a single sheet and a single column ( Column C), which can handle both Deferred or Regular and can give me the output. The user has to refer to cell "E2" in the formula in column C.
How can I do this? Please suggest. The file is attached.
Book1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't see any sense in expecting the value in col. C if the date column is blank. The formula looks at the date column and then add the col. B accordingly based on your condition set in the cell E2.
If you want to see that value in col. C, you will need to extend the dates in col. A down the rows.
If you want to see that value in col. C, you will need to extend the dates in col. A down the rows.
The proposed formula should also work in case the data for some months is not entered.
ASKER
I do not see 1290 with the formula that is suggested. I could however, se all the otehr values. How can I see 1290 , even though there is no month in the Column A,
See "Differed" tab.