Free Microsoft Excel 2013 Quick Reference

Nested IF limit or Open parentheses limit

I have a multi-worksheet cost estimate spreadsheet that has been
developed and enhanced over a number of years. The latest request was
for date cells (Year & Month) to auto-fill with the current year and
month as a prompt for the Project Manager to set them to the correct
value. After some playing around with formats and date calculations
(many thanks to Cpearson's site) I got that sorted out.

I was left with one problem on the capital depreciation worksheet, in
that it returned #Value and not 0 or a correct value as I expected.
Aha, I though, simply put in ISERROR and away we go. That's where I ran
into problems and attach the original base formula below.

What I tried was inserting the Year and Month functions, due to changes
required in cell formats to accommodate the auto completing of
Year/Month info. This works, but resulted in "an error" and the only
way, I could find, round that was to reduce the number of IF's, i.e.
the number of years over which purchases could be depreciated. Given
that we require a minimum of 4 years I am no longer covered, and there
are future requirements to permit up to 25 years depreciation, for
property.

Can anyone help me out of this problem please ?

N15 will end up with 0 or the depreciation value

Current formula results in 0 or the depreciation figure over the
appropriate number of years (up to 6)

N12 is the current year, custom format Number, 0 decimal places
J15 is the years of purchase, custom format Number, 0 decimal places
K15 is the month of purchase, custom format Number, 0 decimal places
I15 is the value of the purchase, Currency, 0 decimal places
L15 is the number of years to depreciate over, Number, 0 decimal
places, numeric value between 1 and 6 years

=
IF(N$12=$J15,(1-($K15-1)/12)*$I15/$L15,
IF(N$12=$J15+$L15,($I15/$L15)*($K15-1)/12,
IF(AND(N$12=$J15+1,N$12


Please see the attached GIF.

I am trying to take information from columns J ("Family Size") and L ("Annual Income"), and figure out how many units should be assigned to columns N ("Very Low Income Units") and O ("Low Income Units").

Essentially, this is what I want to do:

If J2 = 1 and N< 22850, then N2 = F2 and O2 = 0.
If J2 = 2 and N< 26100, then N2 = F2 and O2 = 0.
If J2 = 3 and N< 29400, then N2 = F2 and O2 = 0.

And so on through "8."

I looked at the Nested IF Limitation suggestions on the FAQs . . . it didn't seem like CHOOSE or VLOOKUP would get me where I need to go. Unless VLOOKUP is somehow merged with IF. Does anyone know the easiest way to get this done?

Steven Auto Merged Post;

Oops . . . forgot to attach. See attached.

Hi,

I am having difficulty with what initially seemed a simple problem until I realised I would exceed the nested IF limits.
I have searched for a resolution on this forum but none seems to give me any ideas as to how I could adapt them to solve my problem.
Please see attached spreadsheet, I have explained the problem in full on the spreadsheet itself

Many thanks in advance for any help

rjw

Hi,
I am having difficulty with what initially seemed a simple problem until I exceeded the nested IF limits

Please see attached spreadsheet, I have explained the problem I am having on the spreadsheet.

Many thanks in advance for your help

Hello folks

In the attached spreadsheet I have three columns of figures with each row being a different variation on which column can be greater than which (it will hopefully be obvious from the numbers in the spreadsheet).

Any one of these 6 variations can occur and I need to find the value in the last column. Would it be best to have a nested if statement or, given that for any row, there is only one of two calculations is there any other simpler way which I could find the most appropriate value?

I may not have explained myself very well.

I'm in the process of converting a number of financial spreadsheets from
QuattroPro (QP) to Excel.

I have a ProjIncome_Calculations table on sheet 3. This table has a
different income accural formula for each investment instrument type. Here's
an example . . .

IF(AND(Type="Accrual
Note",Status="Active"),ROUND(IF(PrevCallDate,((((( Face*(PrevCallPrice/100))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+ (((PrevCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price100,DATEDIF(SttlDate,(ProjIncome_Da te+1),"d"),DaysHeld_CrntYr)))-((Cost-Face)+GLAccr_Dec31),2),""))

These accrual figures roll into a Summary sheet (sheet 1) utilizing the
following formula:

IF(Type="Accrual
Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR( Type="BA",Type="BDN"),VLOOKUP(ID,ProjIncome_Calcul ations,3),IF(Type="Bond",VLOOKUP(ID,ProjIncome_Cal culations,4),IF(OR(Type="Equity
Linked Note (A)",Type="STRIP / Coupons",Type="STRIP /
Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5), IF(Type="Equity Linked Note
(B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Type ="Floater
(A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Type ="Floater
(B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKUP (ID,ProjIncome_Calculations,9))))))))

I require 1 more if or to use the =isblank for those lines there the ID
field or the Statis field are blank. Ideally I'd like the formula to Hlookup
the instrument type in the title of the ProjIncome_Calculations table, then
Vlookup the investment ID in column 1 of the table and populate the
appropriate accrual.

Can you help?

PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have
worked beautifully in the past!
--
.. . . Cheers
C.Pflugrath

I need to exceed the 7 nested function limit to extend the following formula:

=IF(RANK(Q$159,$C$159:$S$159)=1,"Leader",IF($C$160=1,$Q$159-$C$159,IF($E$160=1,$Q$159-$E$159,IF($G$160=1,$Q$159-$G$159,IF($I$160=1,$Q$159-$I$159,IF($K$160=1,$Q$159-$K$159,IF($M$160=1,$Q$159-$M$159)))))))

with this,

IF($O$160=1,$Q$159-$O$159),IF($S$160=1,$Q$160-$S$160)

This formula (modified by column) is used for each player in a NASCAR
fantasy league spreadsheet that tracks 9 players total points (Row 159), and
ranks them (Row 160). It determines who is the "Leader" and then subtracts
the players points from the "Leaders" points which is then displayed as -XXX
points behind the leader.

The original formula worked fine until this year when two more people joined
our league. It was then that the 7 nested functions limit stopped my
spreadsheet from working.

I don't know if VBA is the answer or some other worksheet function. I know
next to nothing about VBA but would appreciate any suggestions that would
help me solve this problem. I hope this description is clear, if not let me
know and I will try to clarify it. Thanks.

Has anyone tried to sign up here recently? it is hellish, took me 2 hours, and they asked me if I could count the letters of the alphabet? so patronising? And then I was forced into doing a pol with my question? This is bad, I know I wont get a n answer here, this is substandard.

Well I need some help, just joined, I am a scot had not Jack Daniels..

I work for a national contact centre in UK, keeps me off the streets.. They saw my CV and decided I should do this call centre scoring system in excel? National contact centre for goverment staff scores for call time etc.? I am good on applications thought it would be a breeze, until i came across excels limited nested if functions? I have googled this seen all the solutions? the concantenate stuff, and the naming formulas stuff? naming forumulas stuff does not work? excel makes a mess of your pasted in forumula? you know what i mean i hope.

well here is the brief from my boss guys for the staff scorring system

Re your earlier message about the scoring table for the Productivity v quality chart. I'd like the basic scoring to be the same for all items as follows:-

Score
1 more than 15% lower than all CC average
2 between 10% and 15% lower than all CC average
3 between 8% and 10% lower than all CC average
4 between 5% and 8% lower than all CC average
5 between 2% and 5% lower than all CC average
6 less than 2% worse than all CC average
7 Equal to All CC average
8 up to 3% better than all CC average
9 between 3% and 10% better than all CC average
10 more than 10% better than all CC average

CC means national Call Centre Averages

I've used the words 'better' and 'lower' for want of a better phrased because depending on what we are measuring 'better' could be a higher or lower number. For example for a quality measure such as security the higher the number, the better. However, for WAM error rate the lowest number would represent the best achievement.

Well that amounts to stuff like Call handling time scores, calls per hour, security passes, customer experience, etc etc, the wam error rate is how good or bad someone is at sending accurate emails to the relevant office. We get the figures anyway, I have to do this scoring system.

so I have on my hands a big above the excel 7 nested IFs limitation? They wont move to open source solutions, most of them dont have this limitation of course.I understand ifs logic? but tried all the solutions on the net in google which dont work?

I know you might say, "just do a vlookup"? good answer, I respect you, but can you see tthe logic here? So many Ifs still exist. I wont confess this when I am sober, not that I am not sober now, cough.? But with vlookup I would not know where to start. I am no code man, they have chosen the wrong guy here. I know about applications but? well I am about to lose my job..lol (I think unless I can get my boss pissed and explain that my brain is shot?)

excuse my patter, anyway if anybody can help with this I will buy a jack daniels. But please no links to famous non working solutions..I need my hand held..

greetings from scotland..Mel Gibson is still Willy Wallace, and defeated the english crying freedom..

jackj

Hello,

I have left my brain at home today :

Could someone please advise on how I would achieve this:

I have 10 columns C2:L2

I want to check all of the columns and check if any of the values in these columns are not equal to #N/A or '0'

If any of the 10 columns contain values other than the #N/A or 0 then put the relevant TRUE / FALSE in column M.
I tried:

=IF(C2>0,1,
IF(D2>0,1,
IF(E2>0..... etc etc etc
but I forgot the nested IF limit is 7

Can anyone help me please?

Thanks in Advance.

Hi to everyone! I already search the forum but couldn't manage to find. Please feel free if there is a similiar question that I maybe didn't see.

Below the table what I have been trying to do. Assume that A1=LOW and B10=LOW and in C1 should be LOOSER according to the table below. It goes like this; If A2=LOW;B2=MED the formula should give the answer STAR. All of the formulas totally will be 9 formulas ( which is can work in 2007 w/o a problem, but will cause a problem in 2003).
COST POPULARITY CLASS
LOW LOW LOOSER
LOW MED STAR
LOW HIGH STAR
MED LOW DEAD
MED MED OPP
MED HIGH OPP
HIGH LOW DEAD
HIGH MED OPP
HIGH HIGH OPP
CLASS column should be done by a formula.

this is the formulas that I use in 2007:
=IF(AND(B2="LOW";C2="LOW");"LOOSER";IF(AND(B2="LOW";C2="MED");"STAR";IF(AND(B2="LOW";C2="HIGH");"STAR";IF(AND(B2="MED";C2="LOW");"DEAD";IF(AND(B2="MED";C2="MED");"OPP";IF(AND(B2="MED";C2="HIGH");"OPP";IF(AND(B2="HIGH";C2="LOW");"DEAD";IF(AND(B2="HIGH";C2="MED");"OPP";IF(AND(B2="HIGH";C2="HIGH");"OPP")))))))))

Here is my problem that I already can apply this formulas with all 9 formulas in one Nested IF in 2007. But in workplace we use 2003. And when I try to use/open 2007 with 2003 it always gives an error/problem.

I already try to use "LOOKUP" got an error when it comes to accross formula consisting "HIGH", don't know why though. ( even the IT manager coundn't find. ???)

Tried "VLOOKUP" it works for 3 formulas, and for the rest gives an error.
Tried "CHOOSE" didn't work exactly.
Tried "CONCANTATE" it did work only for first 3-4 formulas.
And last try to use "DEFINED NAMES" I split all 9 formulas 3 seperate group each consist of 3 formulas ad give them a name. But at the end it work for only first group. It can be my mistakes that those formulas didn't work cause I am not expert(beginner)

So need something that can give the result and work in 2003.
Hope that everything clear and understandable because english not my first language.
If someone can help me, I'll appreciate.

I have some data whereby one row has a month, and a corresponding row has hours in that month.

Jan, Feb, Mar, etc
744, 642, 744, etc

When a user inputs the current month in a static cell(or even better when a date fills in automatically using the now statement) I would like to have certain ranges fill in automatically with the correct hours for that month?

I know this could be done with nested IF statements, but it seems that Vlookup could be used as well. I have tried Vlookup, but seem to get it wrong every time.

Any help would be appreciated!

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

..:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

..:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status” is default text that the use can see)
-Submitted
-Not Returned

..:Conditional Results Anticipated:.
If D4=“Select Status”, then E4=””
If D4="Submitted", then E4=display the date & time
If D4="Not Returned”, Then E4=”-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()” function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

..:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

..:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=”” (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

So I've got the following formula to get around the Nested IF limit:

What the formula is supposed to do is check in A1, which has a date, and change the date into the Month #. Once it does that
and finds a match it's supposed to then divide the value in H5 by a certain value in column C of a sheet named plan. The
problem I've run into is that if any of the cells located in column C of the plan worksheet equal 0 or are blank the formula
gives a value of #DIV/0!

For example, if I enter a date of 9/23/08 into cell A1 I will get an error if cell C35 in the plan sheet is 0 or blank, even though the formula should be looking to divide H5/plan!$C$302 since the value of A1 is equal to 9. How can I fix this formula to work? Thanks in advance for any help.

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

..:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

..:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status” is default text that the use can see)
-Submitted
-Not Returned

..:Conditional Results Anticipated:.
If D4=“Select Status”, then E4=””
If D4="Submitted", then E4=display the date & time
If D4="Not Returned”, Then E4=”-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()” function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

..:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

..:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=”” (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

I am trying to create a mileage tracker sheet based upon a pre-set table of
distances. I would like to be able to choose 2 different locations from drop
down lists in 2 different columns, then have Excel input the mileage between
them in a new column and eventually autosum the column. I tried this using a
series of nested IFs then realized I would run into the 7 IF limit. My
problem is that I am not a developer and don't work in VB.
Here are the nested IF statements I was using: rather bulky but they worked:

=IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN",C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="ABR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2)))))))

My problem is that I have to add 3 more ifs in this section alone, then have
the same thing for the 10 other locations. any suggestions would be
appreciated.
--
You want me to do what?!?!

Hello and thanks for reading :-)
I have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro lol)
Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9) and I was wondering if anyone can give me pointers on how to do this without it taking forever!
Thanks
Rachael

Hello,

here's [what should be] a simple formula I ofen struggle with; nested IF statements.

In the attached spreadsheet I need to carry out 15 numerical checks based on what the current value of another field is. Is this possible in a shorter formula to avoid nesting 15 IF AND OR formulas.

As explained in the spreadsheet:

B4 to B18 is static
C4 to C18 is variable
E4 to E18 is variable within a range

Each cell in the range G4 to G18 needs to carry out 15 TRUE/FALSE tests (examples below), but within a single formula

If any one of the tests are TRUE then a return of TRUE is required

Hope someone can help.

Regards,

sherlock99.

Ok this is my first post. I hope I am doing this right.

I think I need to use If And OR functions to do what I want but there is to many combinations and I am not to good at this. So here it goes.

Cell B7 could be SD CC or WO PBM. Cell K3 could be 4 or 5. Now depending on what the two cells contain cell K7 should get its value from one of 4 other cells F2 or H2 or F3 or H3.

Example: If B7 is SD CC and K3 is 4 then K7 should be F2 (15.00) But if B7 is WO PBM then K7 should be F3 (8.40). Now it would change if K3 is 5 that would make K7 choose from the other 2 cells H2 and H3.

I hope I explained this OK. I have tried to do this and I can get it to pick only one set of cells F2 and H2 I think. By now I don't know what I have. This is just some code I have tried. I have had I don't know how many different versions.


	VB:
	
(B7="SD CC",K3=4),F2,H2) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is a my sheet. And it is 11:00 here and I got to get up at 2:00 so I won't be able to post back tonight.

Thank you for your help in advance. And thank you for baring with me.

Keith

I would like the calculated formula in a pivot table to use one percent for a
field name if true or a different percent if false. Below is what I have
come up with but it returns always 100%.

=IF(OR(Offset_AccountName="52 - BALD CUT",Offset_AccountName="72 - WIRE &
NUT",Offset_AccountName="17 - APPLE CORE"),'Weight (oz)'*25%,'Weight
(oz)'*100%)

Hi,

I'm having a problem with an Excel formula containing several IFs and
ORs.

The formula looks like this (the cell references are not important
here, you can simplify it if you like, it is about the structure of
this whole expression in which there must be an error that causes it
not to yield the results that I want):

In one line:
=IF(C21

Hello,
I need some help on a very long nested if formula. Or at least thats the
type that I have been trying to use to get the calculations on my
spreadsheet.

I have this formula which is working for me but it is only one of the 5
variables I need to include in it.
1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) ))

to this formula I need to add

=IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))))
=IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) ))
=IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500) )))
=IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950) )))

So I was wondering if there is a way to have all of these as ONE whole
formula. I will appreciate your help very much.

Thanks!

--
sss

Can anyone help, I need to come up with a formula using the nested IF and OR function for column D labeled family leave. Column B is status(Fulltime or Parttime), Column C is the number of years employed. If the employee is FT and has worked for 1 year or more they get 5 days. If they are FT and worked for less than 1 year or If they work PT but for more than 2 years they get 3 days. Anyone else gets O days. I need help figuring out a formula for this case problem. If you could explain how you came up with the formula. I’m having trouble getting the whole functions part of excel. Thank you.

So let me just start off by thanking anyone who responds. Also, if this requires VBA, I know a bit but not as much as I'd like.

The conundrum (simplified - in reality Columns contain about 2000 rows):

(A1:A5) are all integers
(B1:B5) are all integers
(C1:C5) are all text

I want to create an IF function that looks at a single cell in Column B and checks all the cells in Column A for that same number. I want the true test to render cells Bx:Cx (x=row number) and the false test to render "none"

In my mind this worked:

=IF(A1=OR(A1:A5), B1:C1, "none")

However, Excel wasn't buying it.

Thanks again for the help. Cll

I'm working on a problem and I have tried multiple set ups without success. This is a problem that inspects car loans based on FICO score, determining whether the loan will be safe or a risky one.

The problem:

If a person’s FICO score is 750 or above, use Assessment 1; if 625 or above (up to 749) use Assessment 2; otherwise, use Assessment 3.
• Assessment 1: In the Assessment section of the spreadsheet, you should indicate whether or not this would be a “good” or “safe” loan. If the loan_amount / total_cost_of_the_car is less than or equal to 0.8, it is considered a “good” loan; if less than or equal to 0.9, it is “fair;” > 0.9 to 1.0 is considered “risky.” Have the spreadsheet automatically indicate the appropriate risk category in the Assessment section.
• Assessment 2: In the Assessment section of the spreadsheet, you should indicate whether or not this would be a “good” or “safe” loan. If the loan_amount / total_cost_of_the_car is less than or equal to 0.7, it is considered a “good” loan; if less than or equal to 0.8, it is “fair;” > 0.8 to 1.0 is considered “risky.” Have the spreadsheet automatically indicate the appropriate risk category in the Assessment section.
• Assessment 3: In the Assessment section of the spreadsheet, you should indicate whether or not this would be a “good” or “safe” loan. If the loan_amount / total_cost_of_the_car is less than or equal to 0.6, it is considered a “good” loan; if less than or equal to 0.75, it is “fair;” > 0.75 to 1.0 is considered “risky.” Have the spreadsheet automatically indicate the appropriate risk category in the Assessment section.

Im not quite sure how to organize this within excel, whether I should make a separate sheet for the Assessments (then use Vlookup) or if this is a complicated IF function. I just want one input box where I can type in a FICO score and have one output box display whether its safe or not according to these parameters. Any help would be appreciated!

Hello:

I would appreciate any help I can get at this point. I believe that what I need to do is a nested =If Function in order to do what I want but I have never done that. I am also sure there are other ways to accomplish my goal but Ill let you be the experts.

Simplified:

I am trying to return a list that has unsorted rows (this list is being compiled by use of a formula)

3 1 1 2

2 1 1 3

0 0 0 0

1 1 1 1

3 1 1 2

2 1 1 3

0 0 0 0

In a new sheet I would like to return all rows that do not contain 0 in the first column.

I would like this to be done through the use of a formula since sorting by the first row would unsort the other columns (may not make sense with my example but take my word for it.) So far I have
=IF(Sheet1!C15>0,Sheet1!C15,Keyword1!C16)

The Problem is each time this file is used the
3 1 1 2

2 1 1 3

0 0 0 0

Will be different each time with different amount of rows containing 0 0 0 0.

This may make no sense at all and if it does not... sorry but short of attaching the 20mb file Im lost for ideas.

I appreciate any help.

Thanks,

mmarshall