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

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

- 7 Nested If Limitation
- Nested IF limit exceeded
- Nested IF limit problem
- Nested if statements or something different?
- Nesting if > 7 or using hlookups in a vlookup
- Nested If Limit
- Nested IFs
- Overcome Nested IF formula limit
- Need a nested if formulas or someting will work instead in excel 2003
- Nested IF's? or a better way?
- HELP! Nesting IF Statements/Dependent Dropdown List
- Boolean multiplication to get around IF limit
- HELP! Nesting IF Statements/Dependent Dropdown List
- Get Past the 7 Nested IF limit
- Nested IF/OR formula or vba code instead?
- How can I avoid large, nested IF AND OR queries
- Nested IF(AND(OR Function
- How can I use nested IF using OR in a pivot table?
- Problem with nested IF and OR formula?
- Long Long Long Nested If Function
- I Need help with excel fuctions IF, AND, OR
- Nested If and Or fxn for multiple cells
- Not sure whether this is a nested IF function or not.
- Advanced Filter, nested =IF function, or possibly some other tricker *please help*

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.

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

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

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.

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

=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.

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

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 etcbut I forgot the nested IF limit is 7

Can anyone help me please?

Thanks in Advance.

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.

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!

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.

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.

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.

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?!?!

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

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.

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: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.(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

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

Keith

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%)

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

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

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

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!

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