IFs

Hi Mieko,

This question was posted to our 'Improve the forum' thread, which is not monitored regularly.

I have moved it to our Excel forum and will ask one of our trainers to have a look at it ASAP.

Apologies for the delay.

Regards,
Rich Talbot
STL

You're right, there seems to be a logical error in the first formula (L8) and some room for improvement in the second formula (N8) for calculating monthly amounts in your spreadsheet.

As always, use code with caution.

Let's break them down:

Formula (L8): Prepaid Date

Error: The formula uses <0 (less than zero) to check if the end date (D8) is after the month-end date ($O$1). This should be >0 (greater than zero) for the logic to work as intended.
Calculation: Even with the error, it calculates the difference between the end date (D8) and the month-end date ($O$1) and returns 226 because April 14, 2022, is 226 days after August 31, 2021.

Formula (N8): Release Amount

Logic: This formula calculates the release amount for the month. However, there's a nested IF statement with a confusing structure.
Breakdown:
(J8-($G8*L8/$E8))*-1: This part calculates the remaining amount after subtracting the "prepaid" amount for the month (portion of the total value based on prepaid days).
$G8: Total value (£4660)
L8: Prepaid days (incorrectly calculated due to the error in formula L8)
$E8: Total days in the period (365)
*-1: This multiplies the result by -1 to get a positive value for the release amount since the calculation results in a negative number (money being spent).
IF statement: This checks if the result after multiplication by -1 is less than zero (negative).
If it's negative (meaning the remaining amount is positive), it rounds the result to two decimal places and displays it as the release amount.
If it's not negative (meaning there's no remaining amount), it displays an empty string ("").

Explanation and Improvement:

The first formula (L8) needs correction:

=IF((D8-$O$1) > 0, (D8-$O$1), 0) // Change "<0" to ">0" and add a 0 for no prepayment

This checks if the end date (D8) is after the month-end date ($O$1) and returns the difference or 0 if not.

The second formula (N8) can be simplified:

=ROUND($J8 - ($G8 * L8 / $E8), 2) // Remove unnecessary nesting and "-1"

This directly calculates the remaining amount by subtracting the "prepaid" amount and rounds it to two decimal places.

Using the Formulas for Different Months:

Since you can't change the cell references within the formulas, you'll need to copy and paste them for each month, adjusting the month-end date reference ($O$1) in each formula. For example, for May, the month-end date reference in both formulas would be changed to September 30th, 2021 (cell reference can be adjusted based on your spreadsheet).

This should give you a more accurate calculation of the monthly amounts based on the total value and prepaid days.