Hello! First time poster here, but it appears that this may be a great resource to help me with my Excel woes!
So, I am creating a spreadsheet to track events and averages of the days between those events. What I would like to do is make it so that data (average days between events) is calculated from one date to another if the person is a new intake, and also have the option in the same row for the average amount of days to be calculated if the person is not a new intake but starts with the IES [L]. This means that I will have to have IF statements involved and I do not really know what I am doing. My issue is that there are wacky numbers that come up if data is put in one box, but not another. I have it so that, if the person entering data responds "no" to whether or not the client is a new intake [D], you cannot put any information in the intake date cell [K]. But, when I put in the formula to make it so that crazy numbers aren't generated in the average days between dates cell [M].
I would like it so that, when column D has "yes", a date can be put into column K without anything in column L and it not put in a crazy number. The issue with this can be seen in Row 12.
I also would like it so that, when column D has "no", a date can be put into column L without anything in column K and it also not put in a crazy number. This issue can be seen in Row 11.
I have two different "IF" statements in column M in rows 11 & 12, but I would like to combine those if statements so the crazy numbers aren't generated with either option that is chosen.
Finally, I would like it to calculate in column U the average days between column K to R. This I can do but I separated the average date function for the other option for column V. Then in column W, a number comes out which is calculated to a total average number at the bottom of the spreadsheet. The issue with this is that I only want a number to calculate in EITHER column U or column V, NOT BOTH. Therefore, IF there is a date in column K, I would like it to calculate the average days between dates in columns K & R in column U, while leaving column V blank. Transversely, IF there is NOT a date in column K, I would like it to calculate the average days between dates in columns L & R, while leaving column U blank.
I know this is a lot, but if someone could please provide me some guidance, I will be forever grateful!!!
Please let me know if you have any suggestions or questions! I've attached the spreadsheet here.
Bookmarks