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
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.
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
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.
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
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
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.
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.
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))
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.
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 RelatedI 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.)
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
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
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)))
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,
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
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 RelatedWriting 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] ...........
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] ...........
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.
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
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.
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 RelatedI 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.
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
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 RelatedI 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.
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?
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