# Multiple 'if' Scenarios In A Formula

May 30, 2007
What I am trying to do is a formula for two different 'if' scenarios that will return one value for one answer and another value for the other. They are not numbers, they are text.

For example:

if the cell contains 'pcs' value would = no

if the cell contains a dollar amt value would = yes

View 9 Replies
ADVERTISEMENT
Mar 18, 2014

I have a spreadsheet I work with which contains a formula that needs to take an item ID letter/number combo, substitute the preceeding letter(s) with leading zeros (if the initial numerical characters are fewer than 5) to make the result return a 5-digit number.

The original ID can be in several possible formats (beside each I will post the desired result):

V7684 07684V366 00366V88 00088V60827 60827VS57871 57871VS67 00067

I have the following formula which works like a charm for all except the last example:

=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS","),"V",")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","0"),"V","0"))))

If the first 2 characters are letters ("VS") and there are fewer than 5 numerical characters, the formula results in a 4-digit number, rather than 5. Using the above example, VS67 returns 0067 instead of 00067.

P.S. I just tried

=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS",""),"V","")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","00"),"V","0")))) which only works when there are 3 or fewer numerical characters.

View 6 Replies
View Related
Feb 17, 2009

I'm trying to replicate the VBA code for displaying scenarios using a drop-down list that I found on this website: http://www.tvmcalcs.com/blog/comment...rop_down_list/

However, I've not quite been able to get it to work on my worksheet. This is my

View 3 Replies
View Related
Mar 28, 2009

how i am able to change scenarios using data validation. e.g I have 3 scenarios, i want to be able to show different scenarios using the data validation button. Also in the 2003 excel version i am able to have a scenario toolbar shortcut, how can i have this toolbar shorcut in excel 2007?

2008 Jan Feb Mar.... 2009 Jan Feb Mar..... 2010 Jan Feb Mar.....................

eggs 15 25 35......... eggs 8 11 15............ eggs 96 15 6......................

milk 10 15 20............ milk 65 32 36........... milk 100 36 36......................

butter 5 20 1............ butter 8 18 15......... butter 8 5 45.....................

i want to be able to change values for each year using data validation/drop down list?

1 last question i also want to know how i can circle certain data for training purposes.

View 9 Replies
View Related
Feb 24, 2009

I just want to create a combo box that will allow my manager --- not so good at using menu commands or the scenario manager --- to choose one of five different budget scenarios. I can't seem to get the actual scenarios to change when the selection is made from the combo-box.

This is the

View 4 Replies
View Related
Apr 29, 2008

I have a spreadsheet with 4 columns named:

type

date received

time received

number received

Is it possible to use vbscript and extract the total number received for the various scenarios:

all numbers received

numbers received before specific date

numbers received on specific date before specific time

numbers received after specific date

View 9 Replies
View Related
May 31, 2006

breeze.

Here is my situation:

X Y Z

Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep

A

B

C

The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.

View 9 Replies
View Related
Feb 9, 2014

I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.

I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.

Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.

I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.

View 12 Replies
View Related
Aug 11, 2014

I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.

This is as far as I have got but even this won't work:

=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))

View 7 Replies
View Related
Feb 17, 2010

I have listed data below for purposes of the formula:

HTML CustomerInvoice Invoice Amount

(Rep. Invoice Month)

Nike1 1,000

Reebok1 600

Adidas1 700

Puma1 600

Nike1 (250)

Reebok2 1,000

Adidas2 600

Puma2 700

How do I get the data above, into a spreadsheet like the one below? I have tried If/Than Statements with no luck. I need to total the amount of invoices during a specific month for a specific customer.

View 4 Replies
View Related
Oct 21, 2009

When producing a summary sheet that feeds off several tabs, is there a way I could drag the formula accross with it selecting data from the same cell in multiple tabs?

View 12 Replies
View Related
Nov 29, 2013

I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:

Worker Status %

S1 75% and higher

S2 42.8571% -74.9999%

SH Anything below 42.8571%

My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)

View 4 Replies
View Related
Jan 16, 2014

I need to create a formula that takes into consideration 9 possible scenarios using IF and AND. I have 3 performance measures (exceeds requirements, meets requirements, improvement needed) and 3 potential measures (low, medium, and high). I have a spreadsheet where each individual is rated for both. Each combination correlates to a numeric rating on a 9 box grid. I need to include every option in my formula so the correct rating is determined for each individual. I've tried several versions on my own, and can't get past one set of conditions.

Performance PotentialRating

Exceeds ExpectationsLow4

Exceeds ExpectationsMedium7

Exceeds ExpectationsHigh9

Meets Expectations Low2

Meets Expectations Medium5

Meets Expectations High8

Improvement NeededLow1

Improvement NeededMedium3

Improvement NeededHigh6

Unacceptable Low1

Unacceptable Medium3

Unacceptable High6

View 3 Replies
View Related
Mar 12, 2009

I am not sure what the best way to describe this is but here I go.

Year Account Net Amount

1 2006 Travel Expenses $5,000.00

2 2007 In Kind Time $2,500.00

3 2008 HR Time $3,000.00

4 2006 Travel Expenses $1,500.00

5 2006 In Kind Time $2,500.00

6 2007 Travel Expenses $6,000.00

I need to create a formula that will say: If column A1:A6 has a ,2006, and column B1:B6 has "Travel Expenses", than Sum C1:C6. The answers should be:

2006 Travel expenses = $6,500

2006 In Kind Time = $2,500

2007 In Kind Time = $2,500

2007 Travel Expenses = $6,000

2008 HR Time = $3,000

View 4 Replies
View Related
Sep 23, 2009

I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.

If A1 + B1 = 0 then "0"

OR

If A1 = 0 AND B1 is greater than 0 then give B1's value times .01

If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.

=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))

View 3 Replies
View Related
Jul 17, 2006

How can I make the following formula work =(B3+C3+D3)*E3*F3? I need the

numbers in brackets to be multiplied by E3 and then that number multiplied by

F3,

View 11 Replies
View Related
Dec 22, 2006

I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement:

= IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER"))))))))

But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula

View 9 Replies
View Related
Apr 24, 2008

When you have a total of 11 columns, but 6 of those columns(yellow) can count only as one, and 2 columns (orange)can count as 1, how do you write the formula

View 12 Replies
View Related
Nov 25, 2013

Writing a formula for the info below. Here is what I need basically. If B2>2, then I need it to count all central and field, STK and NSTK in column D.

location

days

stock type

Central

3

nstk

field

1

stk

[Code] ...........

View 9 Replies
View Related
Feb 28, 2014

I am trying to tell the spreadsheet to look at each separate month and return different criteria. Current formula for single month search is:

=IF(VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F","")))))

I would like to add an AND function so that the IF statement also looks at the month which is represented in this case as $E$7, the formula I have been trying is:

=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F")

Then carry this forward for other months, for example, a later look at this formula would be:

=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO

[Coe] ...........

View 2 Replies
View Related
Jun 23, 2014

I have a rota sheet whereby shifts are written in as start time, finish time, and then a calculation for the hours.

At the bottom I have another SUM formula to calculate total daily hours.

However, within the total personal hours I have a VLOOKUP formula to check if the start time cell has a holiday text reference.

Please see below:

Take the "Sergei" example: he starts at 9am, finishes at 8pm, and the formula automatically calculates an 11 hour working day.

However, in the "Tom" example, the "TH" refers to another sheet which houses a reference of the amount of hours to place in the total hours box if those letters appear, thus imputing holiday hours at the same time.

"=IF(ISTEXT(F24),VLOOKUP(F24,'Hol-Meet Hours'!$F$7:$G$27,2,0),(G24-F24)*24-IF((G24-F24)*24>6,0,0))"

What I am struggling with is to use an IF and ISTEXT formula in the "Daily Hours" formula to ignore any holiday inputs, thus only giving me trading hours.

The only way I could think of doing it was something along these lines:

"IF(ISTEXT(F16),SUM(F17:F31),SUM(F16:F31)...et al"

The issue here is that I need to repeat the formula for every staff member within the same cell's formula, which I don't know how to do (multiple functions within a formula repeated 20 times), and is a right drag.

View 5 Replies
View Related
Apr 16, 2006

in cell A1 and cell B1 I have a "yes", "no" or "maybe"

in cell C1 and D1 I have a number

if cell A1 and cell B1 "yes" multiply cell D1 times 2 (all results in cell E1)

if cell A1 "yes cell B1 "no" multiply cell D1 times 1.2

if cell A1 "no" cell B1 "yes" multiply cell C1 times 1.2

if cell A1 and B1 'no" multiply cell cell C1 times 2

if cell A1 "maybe" cell B1 "yes" multiply cell D1 times 2

if cell A1 maybe cell B1 "no" multiply cell C1 times 2

if cell A1 "yes" cell B1 "maybe" multiply cell D1 times 2

if cell A1 "no" cell B1 "maybe" multiply cell C1 times 2

View 6 Replies
View Related
May 11, 2006

I need a formula for:

If nothing is entered into J1, leave it blank

But if J1 is greater than or less than H1, have the text "why?" appear in the cell. Late in the day and getting a lil brain dead.

View 4 Replies
View Related
Aug 9, 2006

I need a formula that will calculate if a Category is at Very High, High, Med, or Low level risk. I've attached a sample file for review. In the sample I use a countif and countblank formula to count the number of specific statuses used. On my summary page I want to use the counts to determine if a category is in one of the 4 risk levels. I've broken down additional information within the attached file.

View 9 Replies
View Related
Mar 8, 2007

I need to create a formula where if (example) letter A was placed in cell A1, cell A2 would assign (example) value of 1, but if letter B was placed in cell A1, A2 would assign different value.

Ideally the formula would be able to handle multiple values from a range on seperate sheet.

View 3 Replies
View Related
Apr 24, 2007

I am trying to calculate phone call rates based on several different conditions. *if my phone call has a "DESCRIPTION" of "Local" then I want to add 2 cents onto the "COST" *if my phone call has a DESCRIPTION of "Mobile" then I want to use the formula = SUM(G7*24*60)*0.4+0.1 to calculate the charges *if my phone call has a "DESCRIPTION" of "13 Number" then I want to add 5 cents onto the "COST" I am able to use a formula that allows 2 condition but I have been unable to work out a formula where I can add more conditions.

I have attached a spreadsheet

View 2 Replies
View Related
Apr 30, 2007

You all were so great last week (thanks Ninja). This is a tracking form for a contest based on restaurant cost results (Page attached). I need a formula for if C2>D2, but less than 1/2%, give100, if C2<D2, give 200.

View 9 Replies
View Related
May 24, 2007

I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.

Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.

View 4 Replies
View Related
Jun 29, 2007

I have a worksheet where there are rows of information in a specific range (A5:G600). I'm using the conditional format of =AND(MOD(ROW(),2), COUNTA($A5:$G500)) to alternate the row colors. I would also need to have the information in column A formatted. Column A contains dates, and if the date is 90 days less than or equal to today's date (90 days or more old) I would need to format that also.

I've tried using both at the same time, but the conditional formats will override each other when applicable. Am I able to do this via conditional formats, or would I need to do one or the other in VBA?

View 8 Replies
View Related
Aug 17, 2007

I'm looking for a formula which will filter the area expiring in any given year per property. An example would be (from the attachment) - what percentage of area of Property 2 is expiring in 2010? Answer is 50.94%.

View 2 Replies
View Related