Jan 04 2024 07:19 AM
I need help to get this function to work correctly! Right now it only subtracts the unit cost (G) if there is a date in (M)I need it to subtract both Unit cost(G) and Repairs invoices(H) from the total. for Line G26.
Does that make sense?
Thanks in advanced.
Jan 04 2024 07:25 AM
SolutionInstead of subtracting, we can sum only the rows for which column M is blank.
=SUMPRODUCT(G2:H24*(M2:M24=""))
Jan 04 2024 07:43 AM
I have 1 more question. Daily interest i have as =(G2*0.0925)/360, How can I get a formula to calculate so the total changes with today's date. So every time i open it the total will be current from date purchased?
Thanks again if you can help.
Jan 04 2024 08:27 AM
If the interest is fixed at 9.25%/360 per day, you can use
=G2*(TODAY()-A2)*9.25%/360
If you want to calculate cumulative interest:
=FV(9.25%/360, TODAY()-A2, 0, -G2)-G2
This will be slightly higher since the interest is added to the unit cost.
(Shouldn't it be 365 or 366 instead of 360?)
Jan 04 2024 08:39 AM
The bank charges by 360. That is what the loan guy said to use for calculations. Could I make this a if function? To add if there is a date in (M) use that date instead of today's date?
Jan 04 2024 09:17 AM
You are so Smart... Thank you Thank you Thank you. Made my life easier.
Jan 04 2024 07:25 AM
SolutionInstead of subtracting, we can sum only the rows for which column M is blank.
=SUMPRODUCT(G2:H24*(M2:M24=""))