Forum Widgets
Latest Discussions
Sliding scale Commission calculation
Hello , I am trying to create a formula in Excel to calculate interpolated commission rates based on a given loss ratio using a predefined table of loss ratios and corresponding commission rates. Here’s the structure of my data: Loss Ratios are in the range B29 to B42. Corresponding Commission Rates are in the range D29 to D42. I want to input a specific loss ratio in cell F1 and calculate the interpolated commission rate in cell F3. Additionally, I want to calculate the final commission amount based on a total premium input in cell F2. Loss ratio(B) Commission(D) 25.00% 42.50% 30.00% 41.25% 35.00% 40.00% 40.00% 38.75% 45.00% 37.50% 50.00% 36.25% 55.00% 35.00% 60.00% 33.75% 65.00% 32.50% 70.00% 31.25% 75.00% 30.00% 79.00% 29.00% I attempted to use the INDEX and MATCH functions for interpolation, but I’m having trouble getting the formula to work correctly. So , if my loss ratio is 45.7346, I want a formula to calculate the commission % . Could someone please provide a concise formula or guidance on how to achieve this? Any help would be greatly appreciated! Thank you!
Extract data from multiple sheet to new sheet in same workbook
I have a workbook with 6 sheets Master Tank List Less than 3000 WC 3000 WC 3200 WC 3400 WC 3700–5000 WC I need to pull data from the 5 WC sheets (excluding the Master Tank List) into a new sheet, keeping the same column names, except for the Quantity column, which I want to exclude. Can someone please guide me on how to do this in Excel?
IFS with VLOOKUP not functioning
IF(OR(IFERROR(IFS(AND(O3=VLOOKUP(O3,'Dropdown list'!BT2:BT3,1),O4=VLOOKUP(O4,'Dropdown list'!BT2:BT3,1)),"yes",AND(O3=VLOOKUP(O3,'Dropdown list'!BU2:BU4,1),O4=VLOOKUP(O4,'Dropdown list'!BU2:BU4,1)),"yes",AND(O3=VLOOKUP(O3,'Dropdown list'!BV2:BV4,1),O4=VLOOKUP(O4,'Dropdown list'!BV2:BV4,1)),"yes",AND(O3=VLOOKUP(O3,'Dropdown list'!BW2:BW4,1),O4=VLOOKUP(O4,'Dropdown list'!BW2:BW4,1)),"yes",AND(O3=VLOOKUP(O3,'Dropdown list'!BX2:BX4,1),O4=VLOOKUP(O4,'Dropdown list'!BX2:BX4,1)),"yes",AND(O3=VLOOKUP(O3,'Dropdown list'!BY2:BY4,1),O4=VLOOKUP(O4,'Dropdown list'!BY2:BY4,1)),"yes",AND(O3=VLOOKUP(O3,'Dropdown list'!BZ2:BZ4,1),O4=VLOOKUP(O4,'Dropdown list'!BZ2:BZ4,1)),"yes"),"no")="yes",IFERROR(IF(AND(O3=VLOOKUP(O3,'Dropdown list'!CA2:CA4,1),O4=VLOOKUP(O4,'Dropdown list'!CA2:CA4,1)),"yes"),"no")="yes"),"yes","no") If the 1st value is in column BT and the 2nd value is in column BT, then return a "yes" if not a "no." This is true for all the following columns. If the 1st value is in column BU and the 2nd value is in column BU, then return a "yes" if not a "no." I am getting no for all arguments except for the 1st column. Any thoughts?Power Query Dropping Decimals with Accounting Format
I'm encountering what seems like a bug in Power Query. I have a table with data that contains numbers with more than 4 decimal places. When this data is in the Accounting format, Power Query is only picking up 4 decimal places, even if the Query is formatting the data is Decimal Number (Changed Type). When the data is formatted as Number, Power Query is able to pick up all decimal places (Some numbers have 10+ decimals) Is this normal behavior? I would like to use the Accounting format because it looks cleaner, but obviously I cannot sacrifice data accuracy. It is also very illogical to have a format labeled "Accounting" only hold 4 decimal places when passed through Power Query I did try restarting Excel multiple times, refreshing queries, the only thing that resolved it was changing the local format to Number.Copying Data from Word to Excel
Hi, I am trying to copy data from Word to Excel. The data in Word seems to be in text format. When I paste it in excel, it gets perfectly copied except the columns. What happens to the columns is that, if there is an empty cell, then it is replaced by the data that is to the right of it. Thus the values in the columns shifts left if there are empty cells. Can anyone please help me sort it out? The volume of data is large, thus manually correcting these is not feasible. I have provided a screenshot of the data that is in word. Here is also a link of the word file - Report.docx Thank you.
Apply a macro to all sheets in a workbook
Good Day, I am trying to apply the below macro to all my sheets in a specific workbook. Any assistance would be helpful. The macro deletes the contents of specific cells if they ARE NOT highlighted yellow. Sub ClearYellow() For Each ws In ThisWorkbook.Worksheets Dim myrange As Range Set myrange = Range("c7:d53,f7:g53,l7:m53,i7:j53,o7:p53") For Each cell In myrange If cell.Interior.ColorIndex <> 6 Then cell.ClearContents Next cell Next ws End SubPrevent external reference in pivot Data Source
Hi, I'm trying to prevent external references being created in new copies of an existing spreadsheet. The issue seems to be limited to the Data Source for pivot tables. My spreadsheet includes various pivot tables based on one big table. That big table is the main thing that gets updated in the spreadsheet. The pivots use the table name (let's call it Big_Table) as the data source, so that the pivots can more easily adjust to changes to the table (additional rows etc). I need to ensure the pivots are always referencing the version of Big_Table that's in the same workbook as them, but I keep running into an issue where - following some kind of change in filename or location - the pivot Data Source changes to an eternal reference (i.e. referencing an old version of Big_Table in a previous version of the spreadsheet). I've tried unticking 'Save source date with file' and ticking 'Refresh data when opening the file' in Pivot Table Options, and a few other things, and I keep thinking I've solved it, then another external reference appears. I'm having difficulty consistently recreating the issue. My latest attempt involves creating a dynamic named range (Range_Big_Table) which refers to Big_Table, then changing the Data Source for each pivot to the dynamic named range. Feels like this should lock the pivots into using the 'local' Big_Table, and I haven't managed to recreate the issue since, but I'm not confident... particularly as when I click Change Data Source (to check the data source for each pivot), it's reverted back to the table name instead of the range name. Can anyone help/give me some sanity? Some maybe relevant extra info: The big table really needs to be formatted as a table, not as a range. The file is generally stored in SharePoint. It may get worked on by more than one person. The file needs to work in 'standard' MS365 Excel without needing Add-ins. The file may get renamed, saved-as and renamed, downloaded from SharepPoint, uploaded or moved to a different SharePoint folder, and the SharePoint folder may also get renamed. I know I can fix this by manually amending the pivot Data Source for each table. That's not scalable or a viable solution for other users of the spreadsheet. I believe I can fix this by moving all pivots to the same worksheet as Big_Table, but that's not viable here. It's a macro-enabled workbook. I've linked a (recorded) 'refresh all pivot tables' macro to a refresh button next to each pivot which seems to work fine. I'd love to replace this in the future with some VBA automation, but p[roper VBA (not just recording a macro) is extra complexity I'm trying to avoid for now if possible. I've messed around with Power Query but it wasn't as flexible as pivots, and caused security warnings, so it's a no-go here. All help appreciated!Y Axis position and data labels
Hi All, I would like to know if it is possible to position a datalabel between the last data point and the Y Axis. Example with datalabels on the left: Same example with datalabels on the right: As you can see the datalabels override the Y Axis label. I would like to move the Y axis at a custom position. More to the right in order to have the datalabel after the last datapoint but before the Y Axis and thus no override. But it does not seem possible. I tried to play with the margin of the Y axis to cheat a little but it is disable. Any idea how to do it ? Thanks, Concaho
