Forum Discussion
if function help
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.
Instead of subtracting, we can sum only the rows for which column M is blank.
=SUMPRODUCT(G2:H24*(M2:M24=""))
6 Replies
Instead of subtracting, we can sum only the rows for which column M is blank.
=SUMPRODUCT(G2:H24*(M2:M24=""))
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.
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?)
