Combining 2 formulas in one cell

[winterjade]

Good Afternoon I am trying to combine 2 formulas:

1. =IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))))
2. =IF(C10="Move Out",(DAY(D10-G1)*65.5))

Into one cell. With trial and error I get a "FALSE" answer when using the foillowing:

=IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))=IF(C10="Move Out",(DAY(D10-G1)*65.5))))

I am looking for either a calculation for the whole monthly fee based on the daily rate of 65.5 time the days in the month (G1= the first day of the month) OR the prorated amount of the move out date minus the first day of the month times the daily rate.

Any help would be greatly appriciated!!
Thank you
Winter

 

[Trevor_S]

Try:

=65.5*IF(C10="Current",[COLOR=#333333]DAY(EOMONTH(G1,0)),IF(C10="Move Out",[/COLOR][COLOR=#333333]DAY(D10-G1),0))

The final ,0 means that if C10 contains neither entry, the result is 65.5*0=0, rather than 65.5*False, which would result in an error.[/COLOR]

 

[dreid1011]

Good Afternoon I am trying to combine 2 formulas:

1. =IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))))
2. =IF(C10="Move Out",(DAY(D10-G1)*65.5))

Into one cell. With trial and error I get a "FALSE" answer when using the foillowing:

=IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))=IF(C10="Move Out",(DAY(D10-G1)*65.5))))

I am looking for either a calculation for the whole monthly fee based on the daily rate of 65.5 time the days in the month (G1= the first day of the month) OR the prorated amount of the move out date minus the first day of the month times the daily rate.

Any help would be greatly appriciated!!
Thank you
Winter

You're almost there. Just make a few adjustments like so:

=IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))=IF(C10="Move Out",(DAY(D10-G1)*65.5))))

=IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))),IF(C10="Move Out",(DAY(D10-G1)*65.5)),"")

And that should do the trick. The end there returns a blank if none of the conditions are true.

 

[winterjade]

YAY This has worked Thank you both for your help...Now that I have it working I realized that I will need another addition (can there be three??) As I realized that if I have someone moving in after the 1st of the month there will be a protation for that as well... the proration formula is Daily rate X # days in month-Move in date+1

So far I am using the first suggestion above:

=65.5*IF(C10="Current",DAY(EOMONTH(G1,0)),IF(C10="Move Out",DAY(D10-G1),0))

Mostly as when I tied the second suggestion I get "you've entered too many arguments" message.

But can I also add an additional formula for the Move in proration?
=IF(C10="Move In",65.5*DAY(D10-G1+1),0)
This formula works on its own, but when I add it to the end of the first suggestion I get a "VALUE" message

=65.5*IF(C10="Current",DAY(EOMONTH(G1,0)),IF(C10="Move Out",DAY(D10-G1),0)),IF(C10="Move In",DAY(D10-G1+1),0)

Thank you again for all your help!!! It is so greatly appreciated!!

Bunches of Thanks
Winter

 

[dreid1011]

YAY This has worked Thank you both for your help...Now that I have it working I realized that I will need another addition (can there be three??) As I realized that if I have someone moving in after the 1st of the month there will be a protation for that as well... the proration formula is Daily rate X # days in month-Move in date+1

So far I am using the first suggestion above:

=65.5*IF(C10="Current",DAY(EOMONTH(G1,0)),IF(C10="Move Out",DAY(D10-G1),0))

Mostly as when I tied the second suggestion I get "you've entered too many arguments" message.

But can I also add an additional formula for the Move in proration?
=IF(C10="Move In",65.5*DAY(D10-G1+1),0)
This formula works on its own, but when I add it to the end of the first suggestion I get a "VALUE" message

=65.5*IF(C10="Current",DAY(EOMONTH(G1,0)),IF(C10="Move Out",DAY(D10-G1),0)),IF(C10="Move In",DAY(D10-G1+1),0)

Thank you again for all your help!!! It is so greatly appreciated!!

Bunches of Thanks
Winter

Ah yes, I see my error. Try this:

=IF(C10="Current",(65.5*DAY(EOMONTH(G1,0))),IF(C10="Move Out",(DAY(D10-G1)*65.5),IF(C10="Move In",65.5*DAY(D10-G1+1),0)))

 

[winterjade]

EEEEEEEEEE, dreide1011!!! You are awesome! I have put many hours into this spreadsheet (and I am just only starting out!) I thank you for your help. I look forward to my end product...which is to have a user friendly excel sheet

I can not thank you both enough for your help today! I am truly grateful <3

Winter

 

[Trevor_S]

Glad that its working. The logic is that each IF formula must have three parts:

• A statement (e.g. C10="Current ")
• The result if the statement is true
• The result if the statement is false.

Where you have multiple statements to check, you only need to check the second statement if the first statement is false. And you then only need to check the third if the second is also false, and so on. So each additional check is embedded into the third part of the previous one. On the final check, you need to put some default value as a third part - i.e. the result if none of the checks are true. In this case we've used 0, so that when you multiply by 65.5 the answer is still 0.

 

[winterjade]

Happy Friday,

Here I am again with a new issue! Seems as I progress new challenges arise! (yay for learning!)

So I am a little hung up on the proration...

Below is what I feel the formula should be, as when looking at the spreadsheet there will be times when I will need a prorated amount and the current monthly amount (this happens when someone moves in mid month) So I am trying to have 2 conditions C15 set to "Move In" AND the month of the date in D15 = the month on the date in G1 (both being June) this will result in an answer of the current charges. (the answer should turn out to be 1923.00) and if the month does not equal the prorated amount will show. Everything seems to work but it looks like excel states I have entered too few arguments.

=IF(AND(C15="Move In",MONTH(D15)=MONTH(G1),64.1*DAY(EOMONTH(G1),(64.1*DAY(D15-G1+1)))

So the whole formula would be:
=IF(C15="Current",(64.1*DAY(EOMONTH(G1,0))),IF(C15="Move Out",(DAY(D15-G1)*64.1),IF(AND(C15="Move In",MONTH(D15)=MONTH(G1),64.1*DAY(EOMONTH(G1,0),64.1*DAY(D15-G1+1),0))))))

Thank you in advance for your help!!
Winter

 

[dreid1011]

Happy Friday,

Here I am again with a new issue! Seems as I progress new challenges arise! (yay for learning!)

So I am a little hung up on the proration...

Below is what I feel the formula should be, as when looking at the spreadsheet there will be times when I will need a prorated amount and the current monthly amount (this happens when someone moves in mid month) So I am trying to have 2 conditions C15 set to "Move In" AND the month of the date in D15 = the month on the date in G1 (both being June) this will result in an answer of the current charges. (the answer should turn out to be 1923.00) and if the month does not equal the prorated amount will show. Everything seems to work but it looks like excel states I have entered too few arguments.

=IF(AND(C15="Move In",MONTH(D15)=MONTH(G1),64.1*DAY(EOMONTH(G1),(64.1*DAY(D15-G1+1)))

So the whole formula would be:
=IF(C15="Current",(64.1*DAY(EOMONTH(G1,0))),IF(C15="Move Out",(DAY(D15-G1)*64.1),IF(AND(C15="Move In",MONTH(D15)=MONTH(G1),64.1*DAY(EOMONTH(G1,0),64.1*DAY(D15-G1+1),0))))))

Thank you in advance for your help!!
Winter

Good morning,

I think you are missing a ) here:

=IF(C15="Current",(64.1*DAY(EOMONTH(G1,0))),IF(C15="Move Out",(DAY(D15-G1)*64.1),IF(AND(C15="Move In",MONTH(D15)=MONTH(G1)),64.1*DAY(EOMONTH(G1,0)),64.1*DAY(D15-G1+1),0))))))

This is what Excel accepted:

=IF(C15="Current",(64.1*DAY(EOMONTH(G1,0))),IF(C15="Move Out",(DAY(D15-G1)*64.1),IF(AND(C15="Move In",MONTH(D15)=MONTH(G1)),64.1*DAY(EOMONTH(G1,0)),64.1*DAY(D15-G1+1))))

When should it return 0?
The blue portion above is where your 0 would have gone. With it in the other place, it was too many arguments for the last IF.