#1
|
|||
|
|||
Average if and sumif
Hi All,
I have two formulas I am trying to achieve and require some help Attached if a sample spreadsheet Please help Also can do a vlookup from a pivot table? |
#2
|
||||
|
||||
The average is given by
Code:
=AVERAGEIF($A$1:$A$15,"=6",$B$1:$B$15) Summing values in N Code:
=SUMPRODUCT(($M$1:$M$10="gen")*($N$1:$N$10))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
As for your last question you could either use the GETPIVOTDATA function ( not very flexible) or have a look at this article.
Using the free POWERPIVOT add-in also has it's advantages, but I can't help you much with that one.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
Thank you
Sum if worked perfectly The average if, seems to work for only one argument, attached this is the actual workbook. Words for 6 but not 7? |
#5
|
||||
|
||||
This has to do with the way Excel treats numbers.
If you format your result with 10 or more decimals, it is not 7 but 6,99999... As you formatted with 0 decimals what you see is 7, but XL sees 6,9999 The workaround is to wrap your formula with the ROUND function ( with 0 decimals). Then all should be OK. Like Code:
=ROUND((A4-FLOOR(A4,1))*24,0) Code:
=ROUND(MOD(A4,1)*24,0)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SUMIF results changed based on filters | SteveBump | Excel | 5 | 10-29-2013 07:25 AM |
Help! 'SUMIF' Formulas | dave1372 | Excel | 2 | 09-08-2011 07:05 AM |
SUMIF Formula question | jcaswell | Excel | 3 | 05-22-2011 02:52 AM |
Need Help With SUMIF Formula In R1C1 Style | Todd | Excel | 9 | 02-27-2010 08:30 PM |
Multiple criteria in SUMIF? | pumpkin head | Excel | 1 | 02-17-2006 09:06 AM |