Solved

Excel

Posted on 2016-08-27
9
40 Views
Last Modified: 2016-09-21
Is is possible to count    Max salary  of Male as well as    Max Salary of Female in an organization. For Exp -
Name Gender Salary
Rohan         M                   60000
Peeyush      M                  40000
Smita           F                   55000
Gauri           F                   35000
and so on. Using Function
0
Comment
Question by:manju shukla
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 90

Expert Comment

by:John Hurst
Comment Utility
I use COUNTIF for this. Set M and F in a column and count the next column using the M/F column as the selector

https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 250 total points (awarded by participants)
Comment Utility
For male =Max(if(B:B="M",C:C))
For female =Max(if(B:B="F",C:C))

Formula should be entered using control shift enter

Also the example is given that gender is in column B and salary is in column C

You can change those range to actual range in your workbook
2
 
LVL 90

Expert Comment

by:John Hurst
Comment Utility
Ha!  Missed the MAX above - sorry
0
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
Comment Utility
Assuming E3=M and E4=F and data is in the range A2:C5 then try this....
In F3
=MAX(INDEX(($B$2:$B$5=E3)*($C$2:$C$5),))

Open in new window

and copy down. No need to use Ctrl+Shift+Enter, Enter alone is sufficient as this is a regular formula.

Also you may use the Pivot Table to get the desired output.

Please refer to the attached for details.
Max-Salary.xlsx
1
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:manju shukla
Comment Utility
Thanks, Thanks a lot. I have got my aswer.
0
 

Author Comment

by:manju shukla
Comment Utility
Grateful for help.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome Manju! Glad we could help.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
you are welcome manju

:)
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
closed as follows
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now