Calculating Amounts and avoiding negative result

Nikhil Patki

New member
STUDENT NAMEAnnual FeeDiscountFinal FeeBook AmtDate1st PayDate2nd PayDate1st Instal Amt1st Instal Bal
Aaroh 2400072023280200015-02-232128003-04-23000
Vedika 26800143425366200020-02-23550003-05-2308455955
Anvee 2250022500200017-02-23750027-04-2307500-2000
Jiggisha 2250022500200021-02-23550004-05-23075000

In the Last column, Instal Bal
I want to avoid -ve numbers.
Presently I'm using the sum formula as such that it shows +ve numbers only if the instalment amount is pending ( receivable)
thus making excess amount showing in -ve.
How can I avoid such -ve amount
and have the excess amount being added in the next instalment balance adjusted,
 
STUDENT NAMEAnnual FeeDiscountFinal FeeBook AmtDate1st PayDate2nd PayDate1st Instal Amt1st Instal Bal
Aaroh2400072023280200015-02-232128003-04-23000
Vedika26800143425366200020-02-23550003-05-2308455955
Anvee2250022500200017-02-23750027-04-2307500-2000
Jiggisha2250022500200021-02-23550004-05-23075000

In the Last column, Instal Bal
I want to avoid -ve numbers.
Presently I'm using the sum formula as such that it shows +ve numbers only if the instalment amount is pending ( receivable)
thus making excess amount showing in -ve.
How can I avoid such -ve amount
and have the excess amount being added in the next instalment balance adjusted,
Hello Nikhil Patki,
Thanks for reaching us. I understand that you want to avoid -ve numbers in the 1st Instal Bal column. You also want the excess amount to be added to the next installment balance when adjusted.
We can accomplish your requirements using two simple formulas using MAX, MIN, and ABS functions. However, I am unclear about the formula you have used to calculate the 1st Instal Bal column values.
To get the actual solution to your problem, please share a sample Excel workbook or the formulas you used in your calculations. Otherwise, adjust your used formulas according to the following generic formulas:
  • For avoiding -ve in the 1st Instal Bal column, you can use-
=MAX(YourPreviousFormula,0)
  • To adjust the excess amount to the next installment balance, apply the following formula-
=ABS(MIN(YourPreviousFormula,0))+NextInstallmentValue
For instance, if you used =D2-SUM(E2,G2,I2,K2) to calculate the 1st Instal Bal value.
tgWJTl7R7fGoZ_2GDqACtW-2fHE9UucW3iAnlIG9ePWyfkAH2m4OFU5Gnqkpk-1TZKd0iu8R52qTDxwmaY5TDwuCEmDdC0bvBRX-KkuP4DjP26YayX7TW3Q0QtDkMAmieDyRmNDi1RL7Y4ygqhu1lNk
To avoid -ve values here, you have to modify the formula to the following:
=MAX(D2-SUM(E2,G2,I2,K2),0)
BG5ubZfntDSdhxFeaSq2l9V7EylD119yHwz-8Y4g5xguDBr-C7p0JQJ2ntavT2hdDOiURfHGHGTFVntXx53ojDeUD_3Rxajixo9kWhFzwo7y27VTPETx5ZsHcc2WkCo6AElbGoRCbq_jbONDKSp1LdQ
And if you enter the next installment value (i.e. 2nd Instal Amt) in column N and want the adjusted value in column O, then you have to insert the following formula:
=ABS(MIN(D2-SUM(E2,G2,I2,K2),0))+N2
99flqfl7QVDXubfSvCMLvFuhHF7SIeY-O9urPsn2Hde8HbdNkAhejgg0hwiN2B96fhgykbY3ub4L4ExxSHOpFtY2DWxTPAxXiXs9ICM7H6lzKp-5U9o5ljKdIouOsVb7OgJdZdrt6fISjE269ZGoLI0

Hopefully, the above-demonstrated formulas will help you to find a solution to your problem. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy
 
Thank you once again, admin. it's really of great help in resolving my issue
now I would face pretty few issues in maintaining my data
and avoid complex formulas to get the column total of these headers.
 

Online statistics

Members online
0
Guests online
22
Total visitors
22

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top