Hello, I just joined today in order to address my problem.
I am using Windows 11, with Office 365; the OS and Apps are fully current with the latest patches, and very recently rebooted.
I have a budget spreadsheet, and it has a tab for Current State of my Debts.
I'm struggling to get just this one part to work; the rest is good.
I have a formula in E70 (=SORT(FILTER($E$4:$G$55,($G$4:$G$55<>0)),2,-1,FALSE)); which correctly produces the following SOURCE data to my problems/questions:
- Col E (rows 70:121) contains Debt Title text
- Col F (rows 70:121) contains the Interest Rate to two decimal places
- Col G (rows 70:121) contains a positive Current Balance value
- This source data is already in alphabetical order by Debt Title text, with relevant debts listed 'at the top' of the list, and blank lines 'falling' out to the rest of the list, at the bottom. The matching cells in F and G are blank, when E is blank.
The desired outcome results in the target area, $AB$4;$AD$55, containing a sorted and filtered subset of data sourced from $E$70:$G$121.
Both the source data ($E$70:$G$121) and target area ($AB$4;$AD$55) are already properly formatted for TEXT (E & AB), PERCENTAGE (F & AC), and ACCOUNTING to 2 decimal places (G & AD).
GOAL#1: In the target area ($AB$4;$AD$55) it should include only data lines at the top of the list from the source area (i.e. blank lines remain blank at the bottom of the list), it is to be sorted FIRST by the Interest Rate in decending order, and then sorted SECOND by the Current Balance, also in decending order.
I have taken incremental steps to place a working formula in cell AB4:
1. This formula works and brings over the list (data lines at the top, blanks below), still sorted alphabetically, as is:
=FILTER($E$70:$G$121,($E$70:$E$121<>""))
2. This formula works and also brings over the list, now sorted by APR only; however, all rows/cells of the target area are filled with something, even when the source rows/cells are blank (i.e. the 'blank' text cells actually contain '0').
=SORT($E$70:$G$121,2,-1,FALSE)
3. This formula works and brings over the list, sorted by APR first, and then Current Balance second; however, all rows/cells of the target area are filled with something, even when the source rows/cells are blank (i.e. the 'blank' text cells actually contain '0'). This is what I am currently living with.
=SORTBY($E$70:$G$121,$F$70:$F$121,-1,$G$70:$G$121,-1)
4. This formula does NOT work, but SEEMS to make sense to me as the solution, yet results in an "#VALUE!" error:
=SORTBY(FILTER($E$70:$G$121,($E$70:$E$121<>"")),$F$70:$F$121,-1,$G$70:$G$121,-1)
5. When I receive the "#VALUE!" error, no manner of troubleshooting helps to resolve the problem, and it really isn't clear to me just WHAT the real problem is exactly.
- Using the "Evaluate Formula" pop-up, when selecting "Show the Calculation Steps" is of no help. The "Evaluate" button faithfully reports the "#VALUE!" error. The "Step in" and "Step out" buttons do nothing.
- "Ignoring the error" does no good, either.
- "Editing in the Formula Bar" is mildly useful. I have ensured as I step through the #4 formula, character by character, that it appears to be syntactically correct, that it is in fact referring to the correct row/cell ranges, as appropriate, as I do visually see the correct source area(s) highlighted. But, I still receive the "#VALUE!" error, every time.
- I have researched via google and its suggested websites how to correctly construct SORT, SORTBY, FILTER and SORTBY(FILTER()) formulas.
- #1 up above does produce the correct subset of the source data, filtering out blank lines.
- #3 up above does produce the correct sorting by APR and then Current Balance.
- Bizzarely, combining the two results in the "#VALUE!" error; yet it appears syntactically correct.
Bookmarks