Can I embed formulas in an Excel cell?
Discussion
Afternoon all,
I'm hoping PH can, as usual, deliver up a far faster response to an Excel query than anywhere else on the net?
I have a bunch of customer contracts where the customer has an annual volume available to them. I know the start date of their contract, and also their ongoing monthly consumption rate, and would like to create a spreadsheet which will automatically tell me the month in which they're going to need to buy additional volume if they're over-consuming at present.
I can easily calculate the number of months their current entitlement will last them, of course, and can use the =EDATE function to automatically calculate the calendar month in which they'll need additional volume based on their contract start date, but what I'd like to do is also use the =ROUNDDOWN function to always round the number of months in which they'll burn through their current entitlement down rather than up, so I'd have a formula looking something like this:
=EDATE(A1,(=ROUNDDOWN(B1,0))
But it seems as though I can't embed one formula into another. Is there any way I can do this without having to include an ugly extra column in my spreadsheet to first round the months down before using them in the EDATE formula?
I'm hoping PH can, as usual, deliver up a far faster response to an Excel query than anywhere else on the net?
I have a bunch of customer contracts where the customer has an annual volume available to them. I know the start date of their contract, and also their ongoing monthly consumption rate, and would like to create a spreadsheet which will automatically tell me the month in which they're going to need to buy additional volume if they're over-consuming at present.
I can easily calculate the number of months their current entitlement will last them, of course, and can use the =EDATE function to automatically calculate the calendar month in which they'll need additional volume based on their contract start date, but what I'd like to do is also use the =ROUNDDOWN function to always round the number of months in which they'll burn through their current entitlement down rather than up, so I'd have a formula looking something like this:
=EDATE(A1,(=ROUNDDOWN(B1,0))
But it seems as though I can't embed one formula into another. Is there any way I can do this without having to include an ugly extra column in my spreadsheet to first round the months down before using them in the EDATE formula?
Stiggolas said:
Add the ugly column and hide it?
Yeah, I will if I have to, I was just hoping for a really clean solution to soothe my OCD! 
In the ideal world I'd have the calculation of the months required embedded into the same formula as well, so if someone asks me how I do it, I can just respond with "it's a box of magic frogs"!
Kermit power said:
Afternoon all,
I'm hoping PH can, as usual, deliver up a far faster response to an Excel query than anywhere else on the net?
I have a bunch of customer contracts where the customer has an annual volume available to them. I know the start date of their contract, and also their ongoing monthly consumption rate, and would like to create a spreadsheet which will automatically tell me the month in which they're going to need to buy additional volume if they're over-consuming at present.
I can easily calculate the number of months their current entitlement will last them, of course, and can use the =EDATE function to automatically calculate the calendar month in which they'll need additional volume based on their contract start date, but what I'd like to do is also use the =ROUNDDOWN function to always round the number of months in which they'll burn through their current entitlement down rather than up, so I'd have a formula looking something like this:
=EDATE(A1,(=ROUNDDOWN(B1,0))
But it seems as though I can't embed one formula into another. Is there any way I can do this without having to include an ugly extra column in my spreadsheet to first round the months down before using them in the EDATE formula?
You only need the = at the start I'm hoping PH can, as usual, deliver up a far faster response to an Excel query than anywhere else on the net?
I have a bunch of customer contracts where the customer has an annual volume available to them. I know the start date of their contract, and also their ongoing monthly consumption rate, and would like to create a spreadsheet which will automatically tell me the month in which they're going to need to buy additional volume if they're over-consuming at present.
I can easily calculate the number of months their current entitlement will last them, of course, and can use the =EDATE function to automatically calculate the calendar month in which they'll need additional volume based on their contract start date, but what I'd like to do is also use the =ROUNDDOWN function to always round the number of months in which they'll burn through their current entitlement down rather than up, so I'd have a formula looking something like this:
=EDATE(A1,(=ROUNDDOWN(B1,0))
But it seems as though I can't embed one formula into another. Is there any way I can do this without having to include an ugly extra column in my spreadsheet to first round the months down before using them in the EDATE formula?
Remove the = in front of the ROUNDDOWN
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff