Troubleshoot:Nested If statements

Nested If statements

Where to begin..
 
I am working on a series of If statements to return a 5,4,3,2,1 value when it is in a certain range.
However.
these ranges are odd.
 
For Instance..
 


%
Rating

100 to 98.01
1

98 to 96.51
2

96.5 to 95.26
3

95.25 to 94.26
4

94.25 to 93.26
5

93.25 to 92.26
4

92.25 to 91.01
3

91.00 to 89.51
2

89.5 or less
1

 
As you can see if the % (we will call $B$17 (in our example) is between either (91.01 - 92.25) or (95.26 - 96.5) then it would reflect a 3 rating..
Each of them except (5) has dual ranges.
Since we are limited in one IF statement to i think 7 i thought I would see if i could break this up into different if statements so that if 5 isn't true then it goes to test for 4 and if 4 isn't true then it would
go to test for 3, etc...
However..
it is difficult to keep it straight when trying to calculate this in an if statement.
Here is an example..
if b17 >=93.26 and b17<= 94.25 then return a 5 else go to next if statement (possibly in another cell).
if b17 is >= 92.26.01 and b17<= 93.25 (OR) b17 >= 94.26 and b17<= 95.25 then return a 4 value or go to next if statements (4test).
if b17 >=91.01 and b17<=92.25 (OR) b17 >=95.26 and b17<=96.5 then return a 3 value or go to next if statement (3test) 
if b17 >= 89.51 and b17<=91.00 (OR) b17>=96.51 and b17<=98.00 then return 2 value else return 1 value.
 
Can you
 
lol..
I know..
pretty crazy..
it gets worse as I have each numeric value setup as a cell reference so if the numbers change over time they can be altered there and not have to be done on all 300+ individual pages where these are transferred to..
 
Does anyone have a better way..
to do this? I will keep working on it..
lol here is kinda what i have so far, but it has errors im still working on..
 


=IF(AND($B$17<=D2,$B$17>=C2),3,G1)

=IF(AND($B$17<=D3,$B$17>=C3),3,G2)

=IF(AND($B$17<=D4,$B$17>=C4),3,G3)

=IF(AND($B$17<=D5,$B$17>=C5),4,G4)

=IF(AND($B$17>=C6,$B$17<=D6),5,G5)

G6 is where the bottom formula starts and then it goes up from there to g5, g4,g3, etc..
 
Here is C&D LOL


Min
Max

98.01
100

96.51
98

95.26
96.5

94.26
95.25

93.26
94.25

 
Confused yet? i think I am..
Oh..
Im not opposed to this being a single line worth of code to copy paste into each one, just not sure thats possible...
can also combine them like 5 and 4 or 5,4,and 3 then 2 and 1 separate..
either way doesn't matter as long
as it works :) I know my formula is wron gsomewhere because each line returned a 3 and the value i had in b17 was a 99 lol so shoulda been a 1 rating..

Anwsers to the Problem Nested If statements

Download SmartPCFixer to Fix It (Free)

Hi,
 
Try this
 
1.
In range G3:G11, enter 0, 89.5, 91, 92.25, 93.25, 94.25, 95.25, 96.5 and 98 respectively
2.
In H3:H11, enter 1,2,3,4,5,4,3,2,1 respectively
3.
In cell G17, enter 93.27
4.
In cell H17, enter =VLOOKUP(G17,$G$3:$H$11,2).  Answer will be 5
 
Hope this helps.

Note: It is recommended that you back up your files and folders before attempting this step:

• Move the mouse cursor over to the upper right side of the screen.
• A menu will pop up on the left. Click on the "Settings" feature on the menu.
• On the Settings window, click on the "Change PC settings."
• Now, left click on the "Update and recovery" feature.
• For the next step, left click on the "Recovery" feature.
• There is an option here saying "Refresh your PC without affecting your files." There, left click on the "Get started" button.
• Now, follow the instructions on the screen to finish the Refresh process.
• After the Refresh process has finished restart your Windows 8.1 or Windows 8 device.
• Check again to see if the error Nested If statements has been fixed.

If you do not possess the technical expertise required to accomplish this yourself or do not feel comfortable doing so, download and install a powerful automated tool to get the job done.

Another Safe way to Fix the Problem: Nested If statements:

 

 

How to Fix Nested If statements with SmartPCFixer?

1. You can Download SmartPCFixer here. Install it on your system. When you open SmartPCFixer, it will perform a scan.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been removed.

Related: 1723 There is a problem with this Windows Installer package A DLL required After installing Windows Vista SP2 my computer turns off while not being in use When turned back on I am asked if I want to start my computer normally BSOD in Windows 8 DRIVER POWER STATE FAILURE due to ntoskrnl exe need help with deciphering minidump Bluetooth Driver Not Connected after Windows 8 1 Install Backup in Windows 7 When I try to backup to my Seagate 3 5 inch push button backup 250 GB 2 0 external hard drive Model ST3250 601U2RK my computer shuts down C Windows Installer wiped Can t get admin tools to work in Windows 7 except config sys Cannot open attachments in Windows Mail Cannot use learn moodle as earlier after the Windows Internet Explorer 9 upgrade Changing letter of Windows 7 system partition Application stop working while running them in Windows 7 Deep Freeze Instalado no Windows 7 um grande problema Dell with preinstalled 64 bit Windows 7 shuts down while booting Desktop Icons disappear when right clicking on desktop in Windows 7 Auto rotate has stopped working on Acer 1820PT running Windows 7 in tablet mode DJ Inkers will not work on Windows 7 Is there a way to make it work,Can I sign into two Windows 8 computers using the same microsoft account at the same time realtek drivers download Did Windows 7 backup my hard drive causing the used space to double in one week from 225 Gb to 450 Gb realtek audio drivers InPageCoFire Error Windows Vista Home Basic realtek hd audio driver Query related to availability of date and time of the replied email from the original email in Windows Mail realtek drivers download Starting display problem Your computer did not shut down properly Windows is collecting information realtek driver Windows 7 logon error realtek driver Windows Update repeatedly offered realtek ac97 audio driver three Access the BIOS via either the F1, F2, Del critical, F10 or F11. To locate out which essential it is, watch the screen whilst your pc is booting to check out what keystroke will get you into the BIOS. Look into the Standard information and facts tab to discover the motherboard’s model and BIOS revision.
Read More: Troubleshooter of Error: Windows Movie Maker patch or fix,How to Fix Problem - capasity of office 2007 excel sheet in xp sp-2?,[Solution] lost bluetooth, webcam, and possibly other items,How to Fix Problem - Keep Getting Error Message That IE has stopped Working?,Troubleshooting:my laptop won't start. it says \"windows failed to start. a recent hardware or software change might be the cause.\" i've tried launch startup repair and it doesn't work.,Hyperlink issues and changes,Windows 7 fault that prevents reinstallation of software,Remote access using Windows 8.1,Can’t browse specific webpages when using Internet Explorer 8,clearing adobe flash player