User Profile
Riny_van_Eekelen
Platinum Contributor
Joined 7 years ago
User Widgets
Recent Discussions
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.Re: Why is Excel treating text as numbers?
Try a Custom Sort. Perhaps that works for you. Let's assume your ProductID's (before sorting and make sure that they are all texts) look like this: Custom Sort as shown above and press OK to get this: Now select the second sort options in the Sort Warning and press OK to get this: As long as all ProductID's are really texts you should get the correct order. If not, best to start a new thread and share an example (upload an xlsx file or share a link that gives access to it on OneDrive or similar).Re: How to insert row below selected row in Excel?
I don't have a solution for your "problem" other than an explanation of the aparent logic applied. Let's say you wan to insert a row so that row 5 becomes empty. Excel's logic says that you select the row where the blank row should appear. That is, row 5. Right-click, Insert and the old row 5 shifts down. If you want the old row 5 to remain and insert a blank row below it, then simply select row 6 and then Insert. The only occasion I'm aware of where Excel offers both the insert above and insert below option is when you work inside a structured table and select the last row of that table and then Insert.
