Formula to offset number of customers

[Peter Andersen]

HI,

My First post on this site, hope someone can help. I am trying to calculate revenue from customers. Normally that would not be a problem, but in this case there is a time difference between the point of sale and the point of revenue generation.

The problem is going from quarters to years. We need to show the first in quarters and the rest in years. I don’t know how many quarters from point of sale to revenue generation, only that it is 1 through 4. The easy way is to convert the following years in to quarters as well, but there has to be a way to calculated this without “cheating”.

Attached file shows how I have used the offset formula to calculate the number of customers. In this excample the customers from Q1 will generate revenue in Q3. The costumers from Q3 will generate revenue in Y2 Q1. Customers from Q4 will generate revenue in Y2 Q2. The customers will be acqueried evenly distributed over the period. Therefore number of customers acquired in the period is dived by 2.

Any help will be much appreciated.
Regards,
Peter

 

[Sajan]

Hello Peter,
Can you upload a file that does not have any macros in it? Alternatively, feel free to copy/paste a few relevant rows directly into the message.

-Sajan.

 

[Luke M]

Macro-stripped version of the file.

 

[SirJB7]

@Sajan, Luke M
Hi!
.xlsm workbook but just a worksheet without any code, neither modules nor forms.
Regards!

 

[Peter Andersen]

Sorry, new file uploaded.

 

[NARAYANK991]

Hi Peter ,

I hope other members who posted here earlier will be able to help ; as for me , I am not able to understand anything. Can you add a few more details ?

1. In which cells do you want formulae ?

2. Can you take any one cell from the range in (1) above , preferably more , and explain what its contents would be , and how would this be derived ?

Narayan