Hello!
Good day! Pls. i'm having trouble adding a formula to put a value every 3 months inside the formula. I have other formula inside but if I add the formula for adding the every 3 months, it is giving me an error.
=IF(BW3<>0,BW3,0)*0+IF(SUM($BV3:BW3)>0,IF(SUM($BV3:BW3)<1.01,$BN3,0))*0+IF(BW$2>=$BJ$3,IF(MOD(MONTH(BW$2)-MONTH($BJ$3),3)=0,$BO$3,"0"),"")
Appreciate your help pls.
Attached is the file.
Thanks in advance.
Last edited by rickyilas; 03-18-2022 at 05:02 AM.
I think it's because you have a number added to a text value. If you change the ending from "0"),"") to 0),0) then I think it should work, if I've understood correctly what you're trying to do.
This formula evaluates to 0+"" which is why you get an error. Not 100% sure what you want to do but you could wrap the whole thing in IFERROR():
WBD
Please Login or Register to view this content.
Office 365 on Windows 11, looking for ✶ rep!
Yes! It's working both formulas. Thanks to all of you.
Really grateful!
Hello,
Just a follow up on this matter pls. There is an issue with the end date. the repeating values every 3 months should stop once there is already an end date.
But the problem is it still continued to be distributed every 3 months even after the end date.
I've attached an example of the file, just that it should end when the date ends.
Thanks again.
Formula:
=IF(ED3<>0,ED3,0)*$BL3+IF(SUM($BW3:ED3)>0,IF(SUM($BW3:ED3)<1.01,$BN3,0))+IF(ED$2>=$BJ3,IF(MOD(MONTH(ED$2)-MONTH($BJ3),3)=0,$BO3,0),0)
Last edited by rickyilas; 05-12-2022 at 12:13 PM. Reason: attach file
Hello,
Just to bring it up in the thread. I have problem with the end date of the formula. There should be an end date for the amount to be repeated every 3 months.
Thanks for your help.
All of the dates in the yellow cells are AFTER all of the finish dates - so what should the finish date be? How is it worked out?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
When do you want it to stop?
I guess you probably want to change:
IF(ED$2>=$BJ3,
to something like:
IF(AND(ED$2>=$BJ3,ED$2<=$BK3),
but you've not really explained what it's doing overall so it's hard to tell.
Thanks for your reply. my apologies for not being cleared. The column reference for end date is in Colum BK pls.
If you can help me edit the formula inside the file would be really grateful.
Thanks.
Last edited by rickyilas; 05-13-2022 at 04:13 AM.
BK1 for all, or the dates from BK3? If the latter, then only ONE of those dates is included in the range highlighted in yellow.
For the first:
=IF(IU$2>$BK$1,"",IF(ED3<>0,ED3,0)*$BL3+IF(SUM($BW3:ED3)>0,IF(SUM($BW3:ED3)<1.01,$BN3,0))+IF(ED$2>=$BJ3,IF(MOD(MONTH(ED$2)-MONTH($BJ3),3)=0,$BO3,0),0))
If it's the second you want:
=IF(IU$2>$BK3,"",IF(ED3<>0,ED3,0)*$BL3+IF(SUM($BW3:ED3)>0,IF(SUM($BW3:ED3)<1.01,$BN3,0))+IF(ED$2>=$BJ3,IF(MOD(MONTH(ED$2)-MONTH($BJ3),3)=0,$BO3,0),0))
Last edited by AliGW; 05-13-2022 at 04:20 AM.
Thanks Aligw, Pls. find attached updated file with explanation. I put the 2nd formula you gave me and it works. Pls. I just need to add 1 more condition inside the formula. I want to add every 74 days an amount of 192x60=11,520 inside the formula. Is it possible?
Glad to help - I am sure someone else will look at this new aim.
Is there anything else you want it to do?
Thanks Aligw for your help. I will wait for someone else to help with that other criteria. You've been a great help. Thank you!
Hello,
Just want to get back to this problem which is not yet resolved. There is a problem that the calculation continues even after the start and end date. I attached the file for your reference pls.
Thanks
Ricky
You need to test that the dates match. In GN6 copied across:
=IF(OR(GN$2<$BJ6,GN$2>EOMONTH($BJ6,0)),0,IF(BW6<>0,BW6,0)*$BL6+IF(SUM($BW6:BW6)>0,IF(SUM($BW6:BW6)<1.01,$BN6,0))+IF(BW$2>=$BJ6,IF(MOD(MONTH(BW$2)-MONTH($BJ6),3)=0,$BO6,0),0))
Hello Aligw,
I copied the formula but I lost the distribution of the R&R (Col BO) every 3 months. and also the monthly salary Col. (BL) which should be distributed between the start & finish date.
I've attached the file with the updated formula.
Thanks
OK - that's me out, I am afraid. Sorry! Someone else will advise.
Hello,
I need help pls. I modify the file to explain further what results i need the formula. Pls. let me know if there is any clarification. I need 1 formula to copy across the table. It is difficult to explain here but it is well explained in the attached file.
Thanks for your help.
Try pasting the following into cell GN3 and then copying over and down:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks Jet! The formula is just incredible and it works!!! Appreciate it very much. I thought nobody could help me, thanks again.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks