# Looking for a excel formula.

Closed
ahamaz Posts 4 Registration date Tuesday April 23, 2013 Status Member Last seen October 31, 2013 - Oct 24, 2013 at 06:59 AM
ahamaz - Oct 26, 2013 at 07:36 AM
Hello,

i have rate sheet of 20,000 entries. rate sheet have data like this.

---------Rate-1------Rate-2-------Rate-3
A-------0.16211
B--------------------0.15645
C-------0.1554
D-------0.3289------0.17732------0.18565
E------------------------------------0.19596
F-------0.1681-------0.1736
G----------------------0.2008------0.18624
H-------0.1718---------------------0.1688
I--------0.1848-------0.1949------0.20321
J-----------------------0.20071

I am looking for a formula that
- First identify the rows that have all three rates like D,I.
- secondly find out highest rate out of three rates like in D:0.3289, I:0.20321.
- last delete the highest rate like 0.3289 & 0.20321 leaving cells blank.

Looking forward to hear from you.
thanks

Related:

## 4 responses

ahamaz Posts 4 Registration date Tuesday April 23, 2013 Status Member Last seen October 31, 2013 1
Oct 24, 2013 at 08:31 AM
Thanks Mazzaropi
actually issue is that there are too many 3 rates that cant be deleted one by one..

if you can please tell me how to do this VBA or if you can do it for me. I will appreciate.

looking forward to hear from you.
thanks
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 24, 2013 at 07:27 AM
Good morning.

Do:
D2 --> =IF(COUNTIF(A2:C2,">0")=3,MAX(A2:C2),"")
Copy it down

This will test your criteria and show highest number.

BUT... to DELETE the highest number it will be necessary to use VBA.

I hope it helps.
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 24, 2013 at 11:22 AM
Good afternoon.

I don't know VBA.
Maybe another user can build the VBA routine for you.

If you want to test some formulas to do your task I did a small example for you:
http://speedy.sh/Epxu9/24-10-2013-en-KIOSKEA-Deleting-maximum-value-OK.xls

Is what you desire?

I hope it helps.
Hi mazzaropi.

there is also another issue if you can please look into it.
I have a table of:

Actual....................................................Output
Names:..............Marks....................Names:......Attempts......Highest Marks
Richard..............0.02476..................Elina...............1.............0.00234
Tommy..............4.87457..................Jack................2............0.03764
Elina..................0.00234..................Michael...........2.............6.87461
Richard..............0.12647..................Richard............4............1.07463
Elina..................0.01324..................Tommy............1............4.87457
jack...................0.03764
Richard..............1.07463
Michael...............6.87461
Jack....................0.00456
Tommy...............0.64789
Richard...............0.12894
Michael..............3.87645

I need output list from actual list:
- Formula shout check the;
1- Same names from the name list like Richard.
2- Count the numbers like 4.
3- Get the heighest marks out of 4 attempts.
4- and display like OUTPUT in alphabetically order name wise.

I will appreciate your efforts, if y look into it.
looking forward to hear from you.
regards
ahamaz