If Statement setting with minimum criteria

Question

With the if statement i am able to create a criteria of reduction as per slab, but in same formula I cannot insert logic of minimum cap. Can you help me with setting up minimum cap formula as well.

(IF(L4>60000,L4*70%,IF(L4>40000,L4*75%,IF(L4>20000,L4*80%,IF(L4<20000,L4*90%,0))))/I4)*K4

using the abve formula I can set reduction criteria but not the minimum cap.

Under 20k - Reduction by 10% of CTC with minimum cap of 10,000

Above 20k and upto 40k - Reduction by 20% of CTC with minimum cap of 20,000

Above 40k and upto 60k - Reduction by 25% of CTC with minimum cap of Rs. 32,000

60k plus and above - Reduction by 30% of CTC with minimum cap of Rs. 45,000

Answer 1

Please explain the meaning of each of the columns:

• What is a CTC?
• Where is CTC on your sheet?
• What is the CAP? Is it the upper limit, ie pro hockey team salary cap, which means a lower "cap" does not make sense? Or is it "capacity" (doesn't fit with a pay scenario)?

Suggestion 1:

• Build your formulas up one step at a time.
• Create "helper" columns outside table that do parts of your calculation, so you can see the results
• build up your more complex formula by referring to the helper columns.
• When you get the correct final result, build the full formula by copying the smaller formulas from the helper columns into a larger formula

The general idea is you start if simple calculations that you can easily cross check, then calulation at a time you merge them into the larger single formula. You can see when something goes wrong and you know what you just changed.

Suggestion 2:

Convert your data into an Excel "Table". It is simple, use the <CTL><T> shortcut

Hint: make your column names descriptive and easily understandable to you and anyone else working on the sheet later.

Tables use "structured references". The formulas are built using column names. It makes it easier for you, and everyone else to understand what is being done in the formula

Suggestion 3:

Upload a small example spreadsheet with some test values and manually calculated example values (outside of the table so we can know if we get the calculation correct

Suggestion 2 tips

!00 Creating an Excel Tablehttps://www.contextures.com/xlExcelTable01.html
Use Excel's Table command to convert a list of data into a named Excel Table. These tables have useful features, like sorting and filtering, to help organize and view data.
What is an Excel Table?--------------------------- -------------------------------- Preparing Your Data
Create an Excel Table ----------------------------- --- Create an Excel Table With Specific Style
Sort and Filter the Data----------------------------- ---------------------------- Rename an Excel Table
Show Totals in a Table----------------------------- -------------- Refer to Table Column in Formula
Add a Counter Field--------------------------------- ------------------------------ Print Excel Table Only
Table Doesn't Expand For New Data---------- ----------------------- List All Tables in Workbook
Download the Sample Files----------------------- ------------------------------------- Related Tutorials
.

!01 Migrating to Tables in Excel ****https://excelsemipro.com/2011/03/migrating-to-tables-in-excel/ ****My first epiphany with Excel was realizing how powerful it could be if you put all your data into data tables. Over the years Excel began to incorporate data tables. I’m going on quest. To find out all I can about Tables in Excel, both good and bad. Let the chips fall where they may. Yet this can’t be done in one post. So I’ll start now and continue with a little mini-series on Tables until I’ve satisfied my curiosity or I’m comfortable using them on a regular basis.
.
Table Design in Excel      2011 03 30
https://excelsemipro.com/2011/03/table-design-in-excel/
I want to start using Excel Tables when building my spreadsheets. But first it’s instructive for me to explain how I think about “unofficial” tables or data tables as I like to call them.
.

Formulas and Structured Data in Excel Tables           2011 04 00
https://excelsemipro.com/2011/04/formulas-and-structured-data-in-excel-tables/
I created a data table on a worksheet, then converted it to an Excel Table. Before the conversion, I had entered a formula for Gross Product in the last column. This formula looks like most formulas do in Excel, with cell references. The great thing about converting to a Table is that when I add more data to the table, the formula is automatically copied down for me. If I change this formula, then Excel will automatically copy the change to all rows in the column. And it doesn’t matter if I change the first cell at the top of the column or change a cell in the middle, all rows in the column will be modified. Tables have a structured data format you can see when adding a formula after the Table has been created. In my example I will reenter the Gross Product formula so that it equals Net Product plus Waste Setup plus Waste Run. Instead of cell references, Excel uses column references for the Table.
.  *  Using Column References in a Table Formula
.  *  Formulas Outside the Table Range
.
!03 Overview of Excel tables ****https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).
.

!04 Excel Tables at excelcampus-Tutorial Beginners Guide for Windows 2007-2013& Mac 2011.mp4  16min (downloaded) (and example files)
https://www.excelcampus.com/tables/excel-tables-tutorial-video/ ---------------- 2013 10 01----------------------------------------- Jon Acampora
10 Awesome Reasons to Use Excel Tables
  1. Automatic Formatting ------------------------- -------------------   2. Organizing & Naming Data
  3. Sorting & Filtering ------------------------------ ---------------   4. Auto Expansion & Navigating
  5 Total Row ------------------------------------------ -----------------------------   6. Remove Duplicates
  7. Create Unique List ----------------------------- ------------------------   8. Pivot Table Integration
  9. Chart Integration -------------------------------- 10. Table Formulas (Structured References)
.
!06 Avoid Writing Complex Excel Formulas – Use Tableshttps://www.myonlinetraininghub.com/avoid-writing-complex-excel-formulas ****There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise or extract data from poorly structured workbooks. I’m not talking about relatively simple nested functions like INDEX and MATCH, I’m talking about monsters. the true Excel Guru knows that if the data were in a Tabular format we’d be able to use the amazing tools Excel has available. Tools that make mincemeat of complex formulas like the one above.
.

.

Suggestion 3 Tip

****************** UPLOAD EXAMPLE - TROUBLE SHOOTING - SHARE PERSONAL ONEDRIVE FILE (NOT BUSINESS ONEDRIVE)

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

Trouble Shooting - Share OneDrive Filehttps://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

.

.

The article includes links to macros to randomize text in Word and numbers in Excel to preserve privacy

.

**************************************

.

Answer 2

Formula in cell M4:

=MIN(IF(L4>60000,MAX(L4*0.7,45000),IF(L4>40000,MAX(L4*0.75,32000),IF(L4>20000,MAX(L4*0.8,20000),MAX(L4*0.9,10000)))),L4)/I4*K4

Please note that a MIN is used so as to not have the salary exceed L4 viz. refer cell M12 / N12. Cell M12 returns 1,600 ignoring the cap of 10,000 because the salary in L4 is less than 10,000 while cell N12 (without considering the MIN) returns 8,000 after taking into account the cap to be 10,000.

Regards,

Amit Tandon

www.excelanytime.com/

Answer 3

Suggestion 1:

• Build your formulas up one step at a time.
• Create "helper" columns outside table that do parts of your calculation, so you can see the results
• build up your more complex formula by referring to the helper columns.
• When you get the correct final result, build the full formula by copying the smaller formulas from the helper columns into a larger formula

The general idea is you start if simple calculations that you can easily cross check, then calulation at a time you merge them into the larger single formula. You can see when something goes wrong and you know what you just changed.

Earlier I made the above suggestion. If you are interested, here is a short 10 minute youtube demonstrating how a complex formula can be slowly built up from smaller pieces. The video is about PowerBI so most of what he says will be gibberish to you. The specific features and techniques are not important. What I suggest is that you pay attention to the process he goes through. He starts with a small, simple results he tests and verifies. Then he combines the separate small results/calculations to build the more complex final result. Trying to build the full, complex formula all at once is fine for an expert who speaks fluent Excel "Function" language. For the rest of us, it is better to sneak up on the answer we are looking for, using small, simple test data sets and simple functions that are built up.

Filter One Slicer Based on Another Slicer in Power BI (Use a Measure) 2021 07 13           Matt Allington
https://exceleratorbi.com.au/filter-one-slicer-based-on-another-slicer-in-power-bi/
https://youtu.be/q0HaNApY7xM              10min16
This is a follow up article from my last blog post where I demonstrated how easy it is to use two slicers to compare any two items. In the demo in my article, I specifically showed how you could compare any 2 years of choice, but the principle applies to any two products, any two customers, or any two of anything. At the bottom of that article, there was a comment from Artur asking if there was a way to limit the choices in the comparison slicer to exclude the item selected in the first slicer.
E.g.If I select Year = 2016 in slicer 1 below, then I don’t want to see Year = 2016 in the comparison slicer 2 below.  I thought that was a great question/suggestion and hence that is the topic for today.
.

Answer 4

• What is a CTC? Cost to company
• Where is CTC on your sheet? Column L Basic Salary
• What is the CAP? Is it the upper limit, ie pro hockey team salary cap, which means a lower "cap" does not make sense? Or is it "capacity" (doesn't fit with a pay scenario)? it is minimum salary. Even if by calculation of voluntary Pay cut % of the mentioned slab goes below the amount, they will get the said minimum amount