looking for a excel formula.Closed

Question

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

Configuration: Windows 7 / Internet Explorer 10.0

Mazzaropi · Answer

Good morning.

Suppose your data at A2:C11

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.

ahamaz · Answer

Thanks Mazzaropi
I appreciate your quick reply and yr efforts.
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 · Answer

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.

ahamaz · Answer

Hi mazzaropi.
thanks for your last reply. it has resolved my problem.

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

Looking for a excel formula.

4 responses

Similar discussions

Subscribe To Our Newsletter!