I have attached a example where i want to get max values from column a & b
i have manually typed the output i require in column d.
The problem is if the output is between 1-9 then output shpuld be 01, 02.....so on.
After 9 should be 10, 11,....so on.
Pls advise one formula as i have many recors in it.
Try this:
=IF(B1=TEXT(MAX(IF($A$1:$A$6=A1,IF($C$1:$C$6=C1,--$B$1:$B$6))),"0"),TEXT(MAX(IF($A$1:$A$6=A1,IF($C$1:$C$6=C1,--$B$1:$B$6))),"00"),"")
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Which version of Excel are you using?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I get output as 01,02,...so on, but i need only max of 1st 2 rows
if bt1 is 1 & 2, the only 02 should be shown in the max field only that is row 2, i.e. Exactly as in column d
bt1 1 bt-xt
bt1 2 bt-xt 02
can we get it above way.
Enter formula as ARRAY formula (Ctrl+Shift+Enter) as per post #2
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks