User Profile
Riny_van_Eekelen
Platinum Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Bank Reconciliations
I'm guessing that you are NOT working with a structured table. Let's assume that you have a Total formula =SUM(G1:G49) in G50. Then you can't insert row 50 and expand regular SUM function automatically to include the new row 50. One way would be allow for a growing sum range by including an empty row. So, in the above, example, row 49 would be empty and then you insert a row above it. Then the sum range becomes G1:G50. A formula solution would be to insert an OFFSET in the SUM function like this: =SUM(G1:OFFSET(G50,-1,0)) Now you can insert a row above row 50 and the OFFSET will make sum range to end one row above (the -1) the row where the formula sits. Alternatively, use a structured table with a Total row. That will allow you to insert a row below the last row with data and the sum will update automatically.Re: max corresponding to a value which may be in 1 or multiple sets
That formula should work, provided that your objective to find an exact match of -917 in both ranges for column D. There is no match in both you'll get #N/A. Use the optional argument [march_mode] to indicate your intentions is an exact match is not found as illustrated in the screenshot below.Re: F/S presentation
jmarin1949 Just seeing you post now. Three days after you posted it. From what you describe you want something like this: Then use a custom format like this: # ##0_)"$";(# ##0)"$" I don't know how you want to show negatives. I assumed within parentheses. You can always change that of course.Re: Long data copied to wide data
I assume you open the XLSX file in Numbers and that you do not have Excel on your Mac. I haven't used Numbers for years so I don't really know it all that well. But if you can get the data in the form demonstrated in the picture below "Table1" with candidate names on all rows and remove rows with only the names, you can create a pivot table from it (Rows: candidate, Columns: question, Values: sum of Raw). and make sure the raw and max values are numbers not texts. This will do the transposition for you. Now I think you can't do much more with that pivot table in Numbers, other than to copy it and paste it as a regular table. Then you can insert your sub-total columns, percentages and the extra header row for the Max scores for each question. If you have Excel on your Mac, I would skip Numbers altogether. But that's just my personal opinion.Re: Data validation with multiple columns
I would like to add to Hans' solution that you need to create a named range for the first column in the Product table, and point the data validation list to that named range. Then it will automatically expand with the Product table when it is NOT in the same sheet as the cells with data validation. See attached.Re: What was the moment you realized Excel was more powerful than you thought?
I agree with Hans. Pivot Tables were an enormous breakthrough in the mid-nineties of the previous century. I've come to love PQ as well but missed out on it's introduction as I had switched to a Mac in 2011, hating everything that was Windows based :). A couple of years ago I moved back to the PC for 'real' Excel work and 'found' Power Query and Power Pivot. Now I'm using it all the time. And the (more recent) introduction of dynamic array functions was another life-changer.Re: Excel 365: Unable to calculate quarterly data from monthly data
I assume you want to copy this formula horizontally to return the quarterly total. Then, just change the last bit to COLUMN(A:A) and it shall work. The calculated quarter number is either 1, 2, 3 or 4 but COLUMN(AF:AF) and copied right will give you 32, 33, 34 and 35.
