0

I'm looking to find a non-VBA alternative to change the following volatile formula (due to INDIRECT() being used) to a non-volatile one:

=(MAX(A1:INDIRECT("A"&D1))*MAX(B1:INDIRECT("B"&D1)))

...where the cell reference D1 above contains a formula that works out the row number of the last row that contains data in the spreadsheet (6 in the example list below):

    A   B
1 | 4   1
2 |     6
3 | 7
4 | 2   8
5 | 3
6 |     9

Is there any way I can replace the INDIRECT with some other non-volatile function(s)?

pnuts
  • 58,317
  • 11
  • 87
  • 139
gunny78
  • 11
  • 1
  • 2
  • 1
    Please explain what you mean by "volatile", what isn't working? – Matt D. Webb Apr 17 '15 at 16:13
  • @pnuts I think the OP wants to restrict the ranges over which the maxima are taken. – XOR LX Apr 17 '15 at 16:21
  • @pnuts Yes. If D1 contains e.g. 6, then they do not want values beyond A6 and B6 to be considered when calculating the maximum in those two columns. – XOR LX Apr 17 '15 at 16:24
  • @pnuts Sorry - not sure what you mean? – XOR LX Apr 17 '15 at 16:26
  • 1
    Ah, yes! How stupid of me! Apologies, and good point! – XOR LX Apr 17 '15 at 16:29
  • Why you don't want to use `INDIRECT` in your formula? – shA.t Apr 18 '15 at 08:28
  • @Matt Webb. Apologies for late reply. The current formula does work. The problem with using the volatile INDIRECT function is recalculation each time you open the spreadsheet, which then prompts you to save the file even though no changes have been made. This is what I want to avoid. – gunny78 Apr 20 '15 at 10:03

2 Answers2

1

Please try: 

=MAX(A:A)*MAX(B:B)
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Pardon me! you are right, But please add that note to your answer for some guys like me ;). – shA.t Apr 18 '15 at 10:22
  • Hi. This would give the correct result, but as XOR LX stated above, I need to restrict the maxima by using a dynamic range as in my original formula. Just looking for a way to replace the INDIRECT formula with another way of doing this. – gunny78 Apr 20 '15 at 10:09
0
=MAX(A1:INDEX(A:A,D1))*MAX(B1:INDEX(B:B,D1))

Sorry... Don't have time to explain it at the moment but this should do what you want if I understand you correctly.

Gravitate
  • 2,885
  • 2
  • 21
  • 37