• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scheduling

A

abhi2611

Member
Hi,

Could someone please take a look at the attached spreadsheet and help me with a formula to automate the calculation.It would be very helpful to have a formula where I can just copy down and across. I tried INDEX MATCH in combination with Offset.

Any help is appreciated.

Thank you!!
 

Attachments

Hi abhi2611,

Can you explain the logic behind your output as there seems some ambiguity in it.

For product A : Online date is 01-06-2014 and the output data appears from 01-06-2014 . The same is for product B. But Product C,D & E don't follow this trend.

Kindly, explain the logic behind getting the output.

Regards,
 
Formula in I7, copied down and across:
=IFERROR(INDEX($B$7:$F$16,MATCH(YEAR($H7)*12+MONTH($H7)-(YEAR(HLOOKUP(I$4,$B$4:$F$5,2))*12+MONTH(HLOOKUP(I$4,$B$4:$F$5,2)))+1,$A$7:$A$16,0),MATCH(I$4,$B$4:$F$4)),"")
 
Actually, if headers are going to be in same order, can simplify the above formula to:
=IFERROR(INDEX(B$7:B$16,
MATCH(YEAR($H7)*12+MONTH($H7)-(YEAR(B$5)*12+MONTH(B$5))+1,
$A$7:$A$16,0)),"")
 
All-

A small update to my previous request. Could you please take a look at the attached and see if something like this is possible?

Initially I had only one machine coming online. Updated scenario is what if that machine comes online multiple time with the same output?
 

Attachments

You can just keep adding more lines. Bolded parts in formula are what get changed. Formula in I9 of your sample file:
=IFERROR(INDEX(B$9:B$18,MATCH(YEAR($H9)*12+MONTH($H9)-(YEAR(B$5)*12+MONTH(B$5))+1,$A$9:$A$18,0)),0)+
IFERROR(INDEX(B$9:B$18,MATCH(YEAR($H9)*12+MONTH($H9)-(YEAR(B$6)*12+MONTH(B$6))+1,$A$9:$A$18,0)),0)+
IFERROR(INDEX(B$9:B$18,MATCH(YEAR($H9)*12+MONTH($H9)-(YEAR(B$7)*12+MONTH(B$7))+1,$A$9:$A$18,0)),0)
 
Luke,

Thank you for the response. Could you take a look at the attached spreadsheet and see how I could apply this to the scenario i provided. I have a tab named new.

Thanks again for your time!
 

Attachments

Is there a way to achieve the numbers in the box using a formula whitout having to create the waterfall below
Click to expand...
While there *might* be a single formula you could create, I doubt it would be any less calculation intensive, and definitely wouldn't be as easy to understand/debug as the current setup with "waterfall" of numbers. Is there a really strong reason why the waterfall approach won't work?
 
Luke,

Thank you for the quick response. The reason I cant have a waterfall is because the sample file shows only a few years, the file I use to forecast, has close to 50 yrs. Once I have the total output...I run the economics....Without the waterfall my sheet currently ends in column GS. Attached is currently how I do. I take the sumproduct of the output and a reversal(You will see this in the file attached) of the machine coming online. Is there any way I could avoid doing the reversal?

Thanks again for taking the time
 

Attachments

Last edited:
No, with that setup, you need the numbers in reverse order so that the correct column*row combination occurs.
 
You must log in or register to reply here.
Back
Top