Question description:
This user has given permission to use the problem statement for this
blog.
I need a forumula that picks between 3 amounts, based on if it is before a set start date, after a set end date, or between the start and end date
Solved by D. W. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
01/08/2018 - 08:14
Hi
Excelchat Expert
01/08/2018 - 08:15
Could you please share the data, if possible
User
01/08/2018 - 08:15
unfortunately I cant
Excelchat Expert
01/08/2018 - 08:15
Okay
User
01/08/2018 - 08:15
I'm trying to create a table based on the revenue a tenant brings in per month
User
01/08/2018 - 08:15
they have a lease start date and a lease end date
Excelchat Expert
01/08/2018 - 08:16
okay
User
01/08/2018 - 08:16
both the start and end date are in the middle of the month, so the rent they would pay would be prorated and not the full monthly amount
User
01/08/2018 - 08:16
the table lists every month for the term. Below the month it needs to return the revenue for that month
Excelchat Expert
01/08/2018 - 08:17
I did not follow completely
User
01/08/2018 - 08:17
all but the 1st and last month would have the full rent, while the the 1st and last would have the pro rated rent
Excelchat Expert
01/08/2018 - 08:17
Could you explain through dummy data in the sheet on th eirght
Excelchat Expert
01/08/2018 - 08:17
*the right
User
01/08/2018 - 08:20
i need to table to look like that, but I need it to calculate those amount based on a formula and not an input
User
01/08/2018 - 08:20
make sense?
Excelchat Expert
01/08/2018 - 08:20
So you want the output as in row 5 and 6
Excelchat Expert
01/08/2018 - 08:21
is my understanding correct?
User
01/08/2018 - 08:21
yes
Excelchat Expert
01/08/2018 - 08:22
give me sometime to work on it
User
01/08/2018 - 08:22
I want it setup to where when someone enters the rent for that unit and the dates of the term, that it calculates the revenue for each month of the term
User
01/08/2018 - 08:24
would it be some sort of choose function, where if you said if the date is <= start date, it returns the 1st month rent, if its between the start and end date it returns the full months rent, if its >= the last month, the if returns the last months rent
Excelchat Expert
01/08/2018 - 08:25
sort of
Excelchat Expert
01/08/2018 - 08:25
Is there a max date and min date for the timeframe that can be entered in Lease start dat e and lease end date
Excelchat Expert
01/08/2018 - 08:26
First of all, need to figure out to get the months between the dates automatically and then if conditions would be used
User
01/08/2018 - 08:27
the min date would be the start date
User
01/08/2018 - 08:27
the max date would be the end date
Excelchat Expert
01/08/2018 - 08:27
okay
Excelchat Expert
01/08/2018 - 08:28
it will take more time and might have to extend the session by 20 min
User
01/08/2018 - 08:29
what is the charge on that?
Excelchat Expert
01/08/2018 - 08:30
nothing
Excelchat Expert
01/08/2018 - 08:30
for complicated problems the session can be extended upto 60 mins
User
01/08/2018 - 08:38
any updates?
Excelchat Expert
01/08/2018 - 08:39
Yeah... almost done got the excel to create automatic months
Excelchat Expert
01/08/2018 - 08:40
now computing rents
Excelchat Expert
01/08/2018 - 08:40
will take 2-3 minutes more
User
01/08/2018 - 08:46
thanks
Excelchat Expert
01/08/2018 - 08:50
Here you go
[Uploaded an Excel file]
Excelchat Expert
01/08/2018 - 08:52
Let me know if this solves your problem
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
