Get max value on unsorted row with 3 different number groups.

cosima

New Member
The table in Excel 2010 is used to regeister all details for 3 different types of invoices each
in a column and each ID is a sequence within each group. I want to use a formula to solve this.

The groups of invoice ID's :
Type1 range 20000-30000
Type2 range 4000-10000
Type3 range 50000-60000

Excample row should look like this :

 Type Type1 Type1 Type2 Type3 Type1 Type1 Type2 InvoiceId 20000 20001 4000 50000 20002 20003 4001 ClientId 333 444 55 6666 777 888 999 Amount 1220 333 666 555 888 666 333

<tbody>
</tbody>

I have tried using HLOOKUP :
HLOOKUP ("Max-value range";\$B\$2:Z\$3;1;TRUE)+1 to get max value from row 1. THe result is not
as expected so can anyone help me ?

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

QU4487

Board Regular
Try this, is you want to return the Max value for a particular "Type";

Excel 2012
ABCDEFGH
1TypeType1Type1Type2Type3Type1Type1Type2
2InvoiceId200002000140005000020002200034001
3ClientId333444556666777888999
4Amount1220333666555888666333
5
6
7Max Invoice ID
8Type120003
9Type24001
10Type350000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B8{=MAX(IF(\$B\$1:\$H\$1=A8,\$B\$2:\$H\$2))}
B9{=MAX(IF(\$B\$1:\$H\$1=A9,\$B\$2:\$H\$2))}
B10{=MAX(IF(\$B\$1:\$H\$1=A10,\$B\$2:\$H\$2))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope this is what you're looking for.

Q

cosima

New Member
Try this, is you want to return the Max value for a particular "Type";

Excel 2012
ABCDEFGH
1TypeType1Type1Type2Type3Type1Type1Type2
2InvoiceId200002000140005000020002200034001
3ClientId333444556666777888999
4Amount1220333666555888666333
5
6
7Max Invoice ID
8Type120003
9Type24001
10Type350000

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B8{=MAX(IF(\$B\$1:\$H\$1=A8,\$B\$2:\$H\$2))}
B9{=MAX(IF(\$B\$1:\$H\$1=A9,\$B\$2:\$H\$2))}
B10{=MAX(IF(\$B\$1:\$H\$1=A10,\$B\$2:\$H\$2))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope this is what you're looking for.

Q

Hi and thanks for helping but when I put the formula on row 2 (IncoiceID) I get circle ref and that is also a problem I got.

Do you have any proposal to solve it ?

Best regards!

QU4487

Board Regular
Where do you need the formula to be? You're getting a circular reference because the formula is using row 2.

cosima

New Member

ADVERTISEMENT

I need to register each invoice per column by selecting a dropdown with Type1-3 and then get it's next available invoiceID on row 2 for InvoiceID's.

QU4487

Board Regular
I think I get what you're attempting to do. Maybe. You want to keep adding columns to the right of Column A. In Row 1 you then have a drop down of which type it is, and in row 2 you then want your formula. This formula will then be one number higher than the previous highest for that type. Is this right?

The below formula will do this. But it isn't perfect. As you insert the column and paste the formula in you need to make sure that column C stays as column C.

=MAX(IF(\$C\$1:\$I\$1=B1,\$C\$2:\$I\$2))+1

My only other recommendation is to find somebody who can write a macro which does this. Macro's are most definitely not my specialty.

Q

cosima

New Member

ADVERTISEMENT

Hi and thanks for your support but I can't make it work properly. I'm pasting the formula into d2, e2 and onto the rest of the row. Result is not OK ?
Perhaps I have done it wrong ? Also I try to stick with using formulas not makros and Vb.

cosima

New Member
Hi and thanks for your support but I can't make it work properly. I'm pasting the formula into d2, e2 and onto the rest of the row. Result is not OK ?
Perhaps I have done it wrong ? Also I try to stick with using formulas not makros and Vb.

I forgot to feedback that your understanding of how it should work is correct. The other question is did you test your formula in row 2 in the sample table ?

Lokk forward to you and others feedback !

cosima

New Member
 Invoice Type Type1 Type1 Type2 Type3 Type1 Type1 Type2 InvoiceId 20000 20001 #VERDI! #VERDI! #VERDI! #VERDI! #VERDI! ClientId 333 444 55 6666 777 888 999 Amount 1220 333 666 555 888 666 333 ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ Table Types Min ID Max ID Translations : Type1 20000 29999 #VERDI = #VALUE Type2 4000 4999 MAKSA = MAX Type3 50000 59999 HVIS = IF ; = , Formula : MAKSA(HVIS(\$B\$1:C\$1=D1;\$B\$2:C\$2+1;HVIS(D1="Type1";20000;HVIS(D1="Type2";4000;HVIS(D1="Type3";50000))))) translated: MAX(IF(\$B\$1:C\$1=D1;\$B\$2:C\$2+1,HVIS(D1="Type1",20000,HVIS(D1="Type2",4000;HVIS(D1="Type3",50000))))) Error : In d2 warning is given (!) --> A value used in the formula has wrong datatype / In dropdown warning : Wrong value Debug : When step/by step in drop down warning : Evaluate --> #VALUE=Type2 Datatype: D1 and D2 have datatype Standard (Tested with text but same result)

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

COMMENT : I have now tested the formula but it ends with #VERDI = #VALUE. Inside the formula it states "Formula result" correctly but shows #VALUE ???

I will be very glad to receive any help and feedback !!!

Threads
1,136,970
Messages
5,678,895
Members
419,788
Latest member
Mukund2903

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back