Referencing a line item that has formula summary method, not displaying correct number

Options
ShakirHabib
ShakirHabib
edited February 2 in Modeling

Hello,

MicrosoftTeams-image (38).png

In the example image, I am referencing a line item "TV Programming (Final)" in another module using an If statement

IF    XXXX

THEN   XXXX.'TV Programming (Final)'

ELSE    0

However, it isn't pulling 4,063,964,538 as expected. I know its an issue with the summary method being set to formula because other line items are referenced the same way in the target module using sum, and the number will be referenced properly. I can't use sum though, as this will show an incorrect value. Is there something else wrong?

Thanks

Tagged:

Best Answer

  • Dikshant
    Dikshant
    Answer ✓
    Options

    @ShakirHabib - I hope you are referring to this formula. If you enable the summary of this Include in cost book forecast? line item and then enable the summary of the main line item, the formula should work. All line items you're using should have a summary enabled in order to get the correct results.

    image.png

    Can you please share the screenshots without hiding the final numbers. it will help us understand the current behavior.

Answers

  • Dikshant
    Dikshant
    Options

    @ShakirHabib - What's wrong with setting up summary as SUM if, at the end, you want to just sum up the child members? IF ELSE with summary as formula will do the same calculation at parent level and will not give the summed up values. Is there a specific reason you are trying FORMULA?

  • ShakirHabib
    ShakirHabib
    edited February 2
    Options

    @Dikshant Hey - I'm using formula because the line item contains a multiplication calc, and if I change the line item summary to sum for that line, then the product is incorrect. I've included images of the formula for tv programming final and the tv programming lookup line that is used for the tv programming final line. Formula was used because "TV programming (Final)" is "TV programming (lookup)" * "tenure". So "TV Programming (Final)" is using formula and "TV Programming" that is trying to reference it is using sum

    MicrosoftTeams-image (40).png
    MicrosoftTeams-image (39).png

  • MarkWarren
    MarkWarren
    Options

    We can't help without seeing the full context and what the IF statement is doing.
    What value do you get in the target module without the IF condition, just a reference?

  • ShakirHabib
    ShakirHabib
    edited February 2
    Options
    image.png

    Hi @MarkWarren apologies for the lack of context, here is the full if statement of the line item that is causing issues. If I remove the IF statement and reference 'TV Programming (Final)', there is no change and I still get 2,007,545,053.

    If I change the 'TV Programming (Final)' summary method to sum or I reference a different line that uses sum, then the same figure will show in the target module as expected - but if I change 'TV Programming (Final)' to sum, it will give an incorrect value (maybe because there is a multiplication in the formula) so I want to use formula summary method.

    EDIT: I changed the summary method to formula and removed the IF statement and it works now. But the IF statement should be included, is there any way to use IF statement with formula summary? Thanks

Categories