# a max or a max range function

• hi there. i hope that someone can help me with this:
in sheet 1

a b c

1 free reduce paid

2 ? ? ?

note: (free means a numbers between 1000-1999, reduce means a number between 2000-2999, and paid means a number between 3000-3999)

in sheet 2 the numbers

a b

1 john doe 1344

2 jane doe 2416

3 jerry jones 1855

4 thom harry 3664

5 joe smith 3062

6 terry david 2732

now in sheet 1

in cell a2 - i need a formula that will give me the max number that is indicated in sheet 2 for free only-giving me a result of 1855 because it is the maximum number between 1000-1999.

i tried =max('sheet2'!,\$b\$1:\$b\$6)

but it gave me a result of 3664 because it is the max number, but i need the max number between 1000-1999 in a2, 2000-2999 in b2, 3000-3999 in c2 and so on.

i hope i did not confuse anyone.

• Welcome to OzGrid

If your names are in column A and the numbers in column B (rows 2-32 -- row 1 as Headers), then for 1000's use:

=MAX((\$B\$2:\$B\$32<2000)*\$B\$2:\$B\$32)

for the 2000's use (this one must be entered as an array formula (Shift+Ctrl+Enter)

=MAX((\$B\$2:\$B\$32>1999)*(\$B\$2:\$B\$32<3000)*(\$B\$2:\$B\$32))

and for the 3000's

=MAX((\$B\$2:\$B\$32>2999)*\$B\$2:\$B\$32)

See cells E2:E4 in the attached. (EDIT: Attachment removed to save OzGrid space -- See post 8 for updated file)

• hi, thanks for the reply back. I did the formula and it did not work for me. maybe i did something wrong. however, when i looked at your attachment,i noticed that the results did not give the maximum number between the ranges.

i hope i did not touch anything in your attachment as i was surfing.

let me know.

thanks,

• My apologies. You are correct. It turns out that all three formula do need to be entered as array formulas (with a Ctrl+Shift+Enter). I think the new version attached here is correct.

• hi, tom - that helped a whole lot. you are definitely a genius, but guess what! i ran into 2 snags

1. in my second sheet i get a #na because i don't have the number available yet to use and i need to keep that formula in the second sheet because it refers to information in other sheets. when that second sheet shows the #na sign, my max's in sheet 1 will also give me the #na sign. is there a way or is there any type of formula that will take away the #na sign, yet leaving my formula in sheet 2 in place and also be able not to have that #na sign show up as my result in sheet 1 but my actually maxed number.

and

2. Even though i got the max number in sheet 1 which is fine, i also need a formula, or is there a way that will indicate to me the missing numbers in that range, for instance: between 1000 and 1999, in sheet 2, the max number used is 1432, and there are missing numbers of 1211, 1333, 1401. i would like to have the choice of using either the next available max number of 1433 or one of the 3 missing numbers stated above. I just need to know a formula of how i can quickly find those three missing numbers in sheet 2.

thanks a million once again,

you are definitely a genius.

rennoyinc

P.S.: ? - how did you learn all this stuff, i would like to know so that i can learn too!

• Hi,

Re question 1. If you modify your forulas so that they return a number (perhaps 0) that is outside of the range that you are checking, then the #N/A problem should go away. It occurs if a non-numeric entry is in one of the cells that the formula addresses. Using a IF statement in your formulas usually can fix this. The general for is:

=IF(ISERROR("formula as is"),0,"formula as is")

Which basically says If the formula returns an error, put 0 in teh cell, otherwise returnthe formula result.

For example:

=IF(ISERROR(VLOOKUP(A15,A4:B12,2,FALSE)),0,VLOOKUP(A15,A4:B12,2,FALSE))

ALso, for some formulas you can use a "faster" check like ISNA which checks only for #N/A errors, rather than all errors. VLOOKUP, for example always returns #N/A, so the above formula for it could be:

=IF(ISNA(VLOOKUP(A14,A3:B11,2,FALSE)),0,VLOOKUP(A14,A3:B11,2,FALSE))

There is also ISNUMBER which might be useful for this case.

For question 2 there are several possible solutions. Do you need a formula based one (which will be a bit brute force, but which work) or is a VBA macro acceptable?

• Ok, here is a formula method for question 2. Unfortunately, the spreadsheet has gotten too big to post. I can make a miniversion that should be postable if you can't follow the following.

First: on another clean (new) worksheet put titles in row 1 like "Numbers" in A1 and "Present?" in B1. (you don't have to have titles, but I used them and this will keep our referencing alligned.

In cell A2 enter 1000, in cell A3 enter 1001. Select these two cells and grab the handle at the bottom right of cell A3 and drag (autofill) down to cell A3001. This should fill the cells with consequtive numbers from 1000 to 3999.

Now the fun part, we are going to create a dynamic named range for your original number data. In my previous example workbooks they were in column B of Sheet1. I renamed this sheet to "Data" and you'll see this name in the following formulas. Select cell B1 (the heading for the column of used numbers). Then from the toolbar do a:
INSERT > NAME > DEFINE
In the top box enter "MyNames" (without the quotes)

In the lower box enter:

=OFFSET(Data!\$B\$1,1,0,COUNTA(Data!\$B:\$B)-1,1)

You can now refer to your data by this name. If you add data to the column, the name (MyNumbers) will automatically adjust in size (as long as you keep your numbers in consequtive rows).

Now go back to the new sheet with the column of 1000:3999.

In cell B2 enter:

=IF(AND(ISNA(VLOOKUP(A2,MyNumbers,1,FALSE)),A2<Data!\$E\$2),"Missing","")

(Cell \$E\$2 on sheet data is the cell that had the max number used for the 1000s.

Drag this formula down column B until you get to 2000 in column A. Then edit the formula in the B column cell next to the 2000 to have \$E\$3 (the cell with the 2000s max). Again drag this formula down to 3000 in column A and edit that row's column B formula to use \$E\$4. Then double clink the handle on that cell and it should autofill to the bottom of the entries in column A. (If not, then drag it to the end).

Column B should now have "Missing" in column B for whatever numbers are not used and are smaller than the max numbr used. Otherwise the cell will be blank.

Now select column B of the "Missings" and from the toolbar:

DATA > FILTER > AUTOFILTER

From the dropdown arrow in cell B1 ("Present?") select "Missing" and only those numbers that are missing (and available) will show in column A.

Let me know if you would like a small sample sheet posted. Or if you'd like a VBA approach.

AND, per your question as to how I learned ... I've just an Excel user and learn as I go. And I must say I've learned an incredible amount the past year adn a half by hanging here at OzGrid reading threads and tryingto help folks. (I learn best by doing and having real projects to work on or others folks actual questions to work on is really helpful to me.)

• The more I looked, the more the above seemed hard to follow. So, here is a small example that looks at ranges from 100-199, 200-299, and 300-399. I also added one more "feature". I named the three cells that have the max values (cells E2:E4 on Sheet data). Their names are My100s, My200s, and My300s. (Naming these is easy. Just select the cell and INSERT > NAME > DEFINE > enter the name in the top box. Excel will alrady have entered the address in the lower box.

So you will see these names in the "Missing" sheet formulas instead of the the cell refernces.

This attachment pushes Dave's filesize limit, so I am going to remove the two previous attachments (which add no extra information).

## Files

• Ok. You got me going and I couldn't stop playing with your problem. (It provided a good excuse to try a few VBA things I had not done before -- or at least tat I could not remember having done.) The following code, placed into a general VBA module will create a sheet named "Missing" and list the unused numbers there. It assumes that your used numbers list is in column B on a sheet named "Data." WARNING -- if you already have a sheet named "Missing" it will delete your sheet and then recreate its own.

To put the code in a general VBA module, open the VBA editor by right clicking on the Exel Icon at the top left of your Excel window. Then select View Code. (This will put in in teh editor in a Private "ThisWorkBook" module. From the toolbar IN THE VBA EDITOR do a: INSERT > MODULE

This should create a general module and place you in it. Paste teh code below into it.

Then, whiel in Excel do an Alt+F8 to bring up the macro dialog box and selct the "ListMissing" macro (probably the only choice) and click run.