Looking for a excel formula.

[Closed]
Report
Posts
4
Registration date
Tuesday April 23, 2013
Status
Member
Last seen
October 31, 2013
-
 ahamaz -
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

4 replies

Posts
4
Registration date
Tuesday April 23, 2013
Status
Member
Last seen
October 31, 2013
1
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1942
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 2, 2021
145
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.
Posts
1942
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 2, 2021
145
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.
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