Forum Discussion

brandydowns's avatar
brandydowns
Copper Contributor
Mar 31, 2020
Solved

IF Function #SPILL! help

This is for an Excel class. I am supposed to copy the formula exactly as the books says (where I've highlighted in green). The "formula result" is correct in the Function Arguments dialog box but whe...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 31, 2020

    brandydowns 

    Yes, the formula has some assumptions which better to avoid even for Excel 2013. For the modern Excel that causes an error. More correct formula will be

    =IF([@[Paid_Type]]="Paid in Full", [@Amount],[@Amount]*$D$3)

    In brief, [Amount] returns entire column, [@Amount] returns the value from that column for the current row. 2013 returns the latest as well doing some silent work, but better to avoid such assumptions.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 31, 2020

brandydowns 

Yes, the formula has some assumptions which better to avoid even for Excel 2013. For the modern Excel that causes an error. More correct formula will be

=IF([@[Paid_Type]]="Paid in Full", [@Amount],[@Amount]*$D$3)

In brief, [Amount] returns entire column, [@Amount] returns the value from that column for the current row. 2013 returns the latest as well doing some silent work, but better to avoid such assumptions.

  • brandydowns's avatar
    brandydowns
    Copper Contributor
    Mar 31, 2020

    SergeiBaklan Thank you so much! This worked perfectly!

Resources