If And function help required

[frustrated]

Hi Everyone.

I am struggling with the attached spread sheet and though it was time I obtained expert help after many hours of head scratching and lack of sleep.

I would like to automatically calculate the monthly invoice for a project after completion and understand how much money is coming in for all projects each month.

A project can either be paid in 12 monthly payments or as a lump sum. For both options, the first payment is made in the same month the project is completed.

The information in columns B-E automatically comes from the system we use and I would like to keep the formats as they are if possible.

I am trying to find a way to automatically calculate the correctly monthly invoice rather than continue to do this manually which is taking many hours.

I have manually entered the data in the first 2 lines so you can see the results I would expect to be returned.

I am not sure if the way I have set the table out is the best way of achieving the results I require so feel free to suggest alternative options.

I hope the spread sheet is self explanatory but if not, I apologise and will be happy to clarify and questions/points you may have.

Thank you for any help you can give me,

[humdingaling]

using if/and and EOMONTH formula
put into H2 and fill right and down

Please Login or Register  to view this content.

First IF is to determine whether Monthly or lumpsum

Second IF lumpsum
date<eomonth of header + 1 = first day of next month
date>= header (1st of month)

third IF monthly
date< 1st day of next month
EOMONTH+12 >= eomonth header

its a matter of tweaking it to exactly what you want
if you having trouble with the formula just map it out on piece of paper

[frustrated]

Hi humdingaling

Thank you for responding to my question, I really appreciate your help. The formula works a treat, I would never have been able to figure this out and your explanation really helped me to understand what was happening. That is the good news.

Now for the bad. Looking into this further, I would like to split the monthly and lump sum amounts into separate columns and not show the value in the same column. Confused?? So am I which is why I have attached an updated spread sheet that hopefully explains what I require more clearly.

This is entirely my fault and I apologise as I should have thought about what I needed more (I guess my brain was more fried that I thought from trying to figure this out).

I would great appreciate any further help you can give me.

Thank you very much

[jjhayes]

Humdingaling did a great job...I modified his formula as follows:

Please Login or Register  to view this content.

I copied that from H3 to BC17.

It basically adds one more If statement. The new If checks whether the column is a "Single Amount" column or a "Monthly Amount" column. If it is monthly, it performs the calculation (checking the month, dividing by 12, etc.). If it is single, then it checks the month and puts the entire amount in that month.

Hope this works and helps.

[frustrated]

Thanks jjhayes

After looking at the attachment, I can see this works brilliantly. I need to tweak the formula references to work in my master file but I should manage this now from the info you have given.

I appreciate you taking the time to help me out. It is people like yourself and humdingaling that make this forum great.

This forum rocks!

Best Regards (no longer) frustrated

[jjhayes]

Frustrated:

Glad it works. Thanks for the rep!

Please mark this thread as solved if you think everything has been addressed.

jjhayes