The use of Concatenate to bring you a Range

[finaljustice]

Hi there,

I am trying to use CONCATENATE() inside another fórmula to bring my desired RANGE. The problem is that inside the formula my Concatenate range will appear inside speach marks which does not allow the other formula to work. Here is what I mean:
(This is not for VBA just regular formula use, and I am translating the formula from portuguese, so the exact name might not be correct)

=Max(Concatenate($E$2;F$2;":";$E$2;F$3))

Which doesn't work and appears when pressing F9 function to read whats in the formula:

=Max("C4:C6")

Now I would like to know if there is a way for me to make it understand that I want to remove the " " so that the =max() formula can work.


Basically what I'm trying to do is, I have a list from 2002 up until the date of today (monthly sales), and I'm using this concatenate to get the maximum values for every quarter of the year. So I could get 2002 1st Quarter, 2nd Quarter, 3rd Quarter and 4th Quarter maximum values up and until the present day.

Any idea if the way i'm trying to is possible?
Any other ideas of how to do this?

Thank you for your atention.
Finaljustice

 

[shg]

You could use INDIRECT to convert a string that looks like a range reference to a range reference, but why not just list the specific ranges inside the MAX instead of using Concatenate?

 

[njimack]

=max(indirect(address(4,3)&":"&address(6,3)))

 

[finaljustice]

You could use INDIRECT to convert a string that looks like a range reference to a range reference, but why not just list the specific ranges inside the MAX instead of using Concatenate?

Thank you for answering, well basically I would have to write down the specific range for every quarter. Since the lower range of the cell and upper range moves in 3, I just thought the concatenate would do the job since. (Dunno if I was clear. E.g. my first range would be C4:C6, then C7:C9, since there is a step of 3 for the upper and lower range i just used formulas to make it)

 

[finaljustice]

=max(indirect(address(4,3)&":"&address(6,3)))

Thank you for the reply! It worked perfectly!! Thank you both. This is the beauty of this forum, you learn something every time! Now I know this new formula "indirect"!

Thank you very much!

 

[njimack]

Glad to be of service. Thanks for the feedback.