LinkBack URL
About LinkBacks
I need a calulation for a Median, based on another Columns result, is that possible?
I need a calulation for a Median, based on another Columns result, is that possible?
Please attach a sample workbook and what is the expected result?
State Calc.xlsx
These a 2 small Areas which are separated by the Column C - the entire spread sheet has several hundred appraisal areas with differing row counts
I need the Calculations in Rows 8-13 to be inline within Each row Based on the Col C. Notice that Columns H's Calc depends on the Median of the Ratios of Col C's Appr_Area. You get the picture!!
Last edited by BigDaddyDoty; 06-26-2023 at 09:20 AM.
No I don't get the picture.
I don't see manually added epected results whith an explanation.
I need to know what results you expect where.
What I am looking for is the Calculations in F8 to G13 & N8 to O13 the ones in (RED) to be calculated in each row. The Median Calculation should only calculate the Median value for Each Change in Col C or for each individual Appr_Area. If you look at the RED formulas should kind of explain it. The entire Sheet has Hundreds of individual Appr_Areas the contend with. Normally you could do a sumIF but I couldn't find a MedianIF statement to Calculate Col H with.
Should kinda look like this:
Capture.PNG
Does that help?
Why does the calculation of PRD differ between the first column set and the second column set?
G11:
=AVERAGE(FILTER(G$2:G11,($A$2:$A11<>"")*($C$2:$C11=LOOKUP(2,1/($C$2:$C11<>""),$C$2:$C11))))
G12:
=MEDIAN(FILTER(G$2:G12,($A$2:$A12<>"")*($C$2:$C12=LOOKUP(2,1/($C$2:$C12<>""),$C$2:$C12))))
Select F9 to G13... copy DOWN.
O11:
=AVERAGE(FILTER(O$2:O11,($A$2:$A11<>"")*($C$2:$C11=LOOKUP(2,1/($C$2:$C11<>""),$C$2:$C11))))
O12:
=MEDIAN(FILTER(O$2:O11,($A$2:$A11<>"")*($C$2:$C11=LOOKUP(2,1/($C$2:$C11<>""),$C$2:$C11))))
again, select the relevant block and copy DOWN.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Why does the calculation of PRD differ between the first column set and the second column set?
They are calculated of of the Differing Weighed Avg Values between the Original Total Cost and the Adjusted Total Cost. So the Median Calc for each Appr_area needs to be imbedded into the Calculation in Colum H. The Ratio Calculation s are (Sale_Amount/Total Cost (F/M)) & (Sale_Amount/Adj Val) (F/N))
Needs to be like this
State Calc.xlsx
Upload a file showing what you have and what you want and NOTHING else.
I don't still see manually added epected results of a formula, so I still don't understand what the formula should do.
There does not exists a MEDIANIF, but in 365 its easy to create your onwn MEDIANIF with MEDIAN(FILTER
Please Login or Register to view this content.
For exmaple =MEDIANIF (<Criteria range>,<criteria>,<Median range>) =MEDIANIF(A1:A99,12345,B1:B99) ==> MEDIAN(FILTER(B1:B99,A1:A99=12345))
Last edited by HansDouwe; 06-26-2023 at 01:48 PM.
State Calc.xlsx
Does this help?
N3=IF(COUNTIF($E$3:E3,E3)=COUNTIF($E$3:$E$38,E3),AGGREGATE(16,6,$M$3:$M$38/($E$3:$E$38=E3),0.5),"")
Copy down
Orginal mean, please try in V3 and copy down:Orginal median, please try in W3 and copy down:Formula:
Please Login or Register to view this content.Adjusted mean, please try in AE3 and copy down:Formula:Please Login or Register to view this content.Adjusted median, please try in AF3 and copy down:Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Is there an addon to make the Filter command Work? I get a #NAME? error if I change anything in the Spreadsheet
Filter, works in Excel 2021 and Excel 365 only.
If you get #Name you are using an older Excel Version.
If your MS-Off Ver. in your profile (Office 365) is correct it should work.
I have 365 on my personal stuff, but not my work PC
MS Excel 2016 (16.0.5400.1000) MSO (16.0.5393.1000) 32-bit
In that case the formula will work on your personal stuff, but not on your work PC.
We provide solutions for the MS-Office version that you indicate in your profile.
If you need a solution for a different version or MS-Office, adjust your profile or indicate this very explicitly in your opening post.
I updated my version to work version, do I have to start a new thread? Or will this suffice?
Thanks, this will suffice.
This formula should work in Excel 2016:
Orginal median: Please try in W3 and copy down:and adjusted median: Please try in AF3 and copy down:Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Last edited by HansDouwe; 06-26-2023 at 03:37 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks