SOLVED

What Formula Do I Use To Show A Calculation % For A Range If Result Is BETWEEN Certain Values?

%3CLINGO-SUB%20id%3D%22lingo-sub-2267349%22%20slang%3D%22en-US%22%3EWhat%20Formula%20Do%20I%20Use%20To%20Show%20A%20Calculation%20%25%20For%20A%20Range%20If%20Result%20Is%20BETWEEN%20Certain%20Values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267349%22%20slang%3D%22en-US%22%3E%3CP%3EI%20attached%20a%20sheet%20with%20an%20example.%3C%2FP%3E%3CP%3EWhat%20I%20want%20is%20a%20staggered%20range%20in%20Column%20D%20that%20shows%20a%20certain%20percentage%20based%20on%20the%20previous%20columns%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%22%25%20Increase%20In%20Value%22%20(Column%20C)%20is%20in%20the%20below%20range%2C%20show%20the%20following%20percentage.%20Otherwise%20show%20blank.%20If%20greater%20than%2050%2C%20show%20the%20profit%20%25%20increment%20with%205%25%20increases%20up%20till%2090%25%20max%3A%3C%2FP%3E%3CP%3E100%20-%20125%25%20%3D%2050%25%3CBR%20%2F%3E125%20-%20150%25%20%3D%2055%25%3CBR%20%2F%3E150%20-%20175%25%20%3D%2060%25%3CBR%20%2F%3E175%20-%20200%25%20%3D%2065%25%3CBR%20%2F%3E200%20-%20225%25%20%3D%2070%25%3CBR%20%2F%3E225%20-%20250%25%20%3D%2075%25%3CBR%20%2F%3E250%20-%20275%25%20%3D%2080%25%3CBR%20%2F%3E275%20-%20300%25%20%3D%2085%25%3CBR%20%2F%3E300%20-%20325%25%20%3D%2090%25%3CBR%20%2F%3E325%25%20and%20above%20%3D%2090%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EI%20think%20this%20requires%20the%20nested%20%22%3CA%20title%3D%22Nested%20IF%22%20href%3D%22https%3A%2F%2Fwww.techonthenet.com%2Fexcel%2Fformulas%2Fif_nested.php%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EIF%3C%2FA%3E%22%26nbsp%3B%20or%20the%20%22%3CA%20title%3D%22IFS%20Function%22%20href%3D%22https%3A%2F%2Fwww.techonthenet.com%2Fexcel%2Fformulas%2Fifs.php%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EIFS%3C%2FA%3E%22%20function%26nbsp%3B%20(which%20only%20works%20in%20Excel%202019%20%26amp%3B%20above%2C%20but%20which%20I%20%3CI%3Edo%20%3C%2FI%3Ehave).%20Just%20not%20quite%20sure%20how%20to%20write%20it%20here%20(for%20%3CSTRONG%3Ecolumn%20D%3C%2FSTRONG%3E)...%3C%2FP%3E%3CP%3EI%20need%20help%20to%20correct%20%3CSTRONG%3EColumn%20D%3C%2FSTRONG%3E%20so%20that%20it%20shows%20the%20correct%20percentage%20for%20the%20range%20(it%20currently%20shows%2050%25%20no%20matter%20what).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267349%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

I attached a sheet with an example.

What I want is a staggered range in Column D that shows a certain percentage based on the previous columns result:

 

If "% Increase In Value" (Column C) is in the below range, show the following percentage. Otherwise show blank. If greater than 50, show the profit % increment with 5% increases up till 90% max:

100 - 125% = 50%
125 - 150% = 55%
150 - 175% = 60%
175 - 200% = 65%
200 - 225% = 70%
225 - 250% = 75%
250 - 275% = 80%
275 - 300% = 85%
300 - 325% = 90%
325% and above = 90%

 

I think this requires the nested "IF"  or the "IFS" function  (which only works in Excel 2019 & above, but which I do have). Just not quite sure how to write it here (for column D)...

I need help to correct Column D so that it shows the correct percentage for the range (it currently shows 50% no matter what).

 

Help appreciated.

2 Replies
best response confirmed by Dhira1360 (New Contributor)
Solution

@Dhira1360 Perhaps easiest with a small lookup table as demonstrated int eh attached workbook. end result in column F