Dear All,
I'm facing a problem doing the average of the percentages in columns A to J at the Cell A42 as well as with the IF a value is more than 21% but no more than 49% the true vale is 2 and if =>50% but less than 69% the true value is 3
The problems I'm facing are in Cells: A42, L3 and L42
Can you help?
Kind Regards
Fausto
Alexandre School Rates.xlsx
Fausto Cruz
=AVERAGEIF(A3:A39,"<>"&0,A3:A39)
As for the second issue, you could change your table in col X:Y to only show the minimum values, i.e.
0% 1
21% 2
50% 3
70% 4
90% 5
Then use the formula
=INDEX(X:Y,MATCH(A42,X:X,1),2)
IF Statement
=IF(A3<20%,1,IF(A3<49%,2,IF(A3<69%,3,IF(A3<89%,4,5))))
For AVERAGE use
=AVERAGEIF(L3:L39,"<>0",L3:L39)
Change the formula in L3 to
=IF(A3<20%,1,IF(A3<49%,2,IF(A3<69%,3,IF(A3<89%,4,5))))
For A42 and L42 use the first formula in brokenbiscuits' reply.
If you need it to work with older versions of excel (2003 or older) then keep your existing formula in A42 and L42, but select the cell, press f2, then hold down Shift & Ctrl, and press Enter to confirm it as an array.
You need to do this for each formula.
I thank you all very much, it is solved.
Kind regards
fausto
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks