Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

IF statements going wrong

Posted on 2006-06-19
4
Medium Priority
?
239 Views
Last Modified: 2010-04-17
I have a client who is using if statements to find out totals of sales stats for each of their sales people. When he uses an IF statement within a SUM formula it works fine by picking up the sales person and summing their figures, but when he tries to put an IF statement in any of the other formulas it does not give the right infomation....using formulas in the form {=AVERAGE(IF(A4:A11=A2, B4:B11, 0))}

A                       B      
Salesperson1            
            
Salesperson1      13      
Salesperson1      15      
Salesperson1      16      
Salesperson1      23      
Salesperson2      3      
Salesperson2      3      
Salesperson2      3      
Salesperson2      3      
            
67      SUM(IF(A4:A11=A2, B4:B11, 0))      
8      AVERAGE(IF(A4:A11=A2, B4:B11, 0))      
8      COUNT(IF(A4:A11=A2, B4:B11, 0))      
23      MAX(IF(A4:A11=A2, B4:B11, 0))      
0      MIN(IF(A4:A11=A2, B4:B11, 0))      

does this make sense to anyone?

0
Comment
Question by:Microtech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
Nat_c earned 1000 total points
ID: 16935219
Excel has specific formula to use for goal your trying to acheive, try using

sumif
daverage
countif
dmax
dmin

They allow you to specify criteria to apply to the sum, count, etc

Let me know if you need help with the syntax of these

Nat
0
 
LVL 17

Author Comment

by:Microtech
ID: 16935575
sorry this should have been in the excel topic area... passing information on... thanks
0
 
LVL 17

Author Comment

by:Microtech
ID: 16935682
That worked great thank you.
0
 
LVL 4

Expert Comment

by:Nat_c
ID: 16935691
Your welcome
0

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While it is possible to put two routes in place with the secondary having a higher metric, this may not always work. In the event of a failure that does not bring down the physical interface on the router the primary route is not removed. There is a…
There are two basic ways to configure a static route for Cisco IOS devices. I've written this article to highlight a case study comparing the configuration of a static route using the next-hop IP and the configuration of a static route using an outg…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

730 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