Excel Analysis Toolpak
June 18, 2019
I am working on a forecast spreadsheet for a client and need to combine forecast/actuals data. I have been using an IF statement to retrieve the actuals data and then a simple multiplication formula to generate the forecast. It is in a day by day format so all future dates are the multiplication and past are looked up on a separate table.
Here is the syntax =IF(G1>AF4,'CITRIX ACTUALS'!D689,D195*AN75). The reference to cell AF4 is the start date of the report. The only result I get is a "0" even though there is data in the forecast information.
July 13, 2016
Let's just break down this formula so I can understand a little better...
=IF(G1 (a date on the current sheet) is greater than AF4 (the report start date on the current sheet) then go to sheet "CITRIX ACTUALS" and return the value of D689 (from that sheet) ... but if the date in G1 is not greater then return the value from D195 (on this current sheet) multiplied by the value in AN75(on this current sheet).
Is it correct that the only reference you want to make to the CITRIX ACTUALS sheet is for the D689 value, or should the D195 value come from there too?
I think it is worth checking the value in the AN75 cell (or the whole AN column depending on how you are managing this formula) to ensure you don't have values there that will cause a zero result.