Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Formula- If Between Add


I am having a few issues stringing along a formula.

A1 5

I need A2 to say "If A1 is between 0 and 2 add 0, if greater than 2 but less than 4 add .30, if greater than 4 but less than 6 add .70 and so on.

A2 looking at A1
if 0 through 2 add 0
if 2 through 4 add .30
if 4 through 6 add .70
if 6 through 8 add 1
if 8 through 10 add 1.3
if 10 through 12 add 1.6

Basically these are employee break times that I need to automatically figure into our labor hours. Every 2 hours they get "x" amount of break time. So if they have over 2 hours of work they will get 1 break, if they work over 4 hours then I factor in a lunch and the previous break ect..


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.

I've got a formula

=IF(COUNTA(A1)=1,TODAY(),"")

When something is typed in Cell A1 the cell with the formula will input todays date.

Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.

View Replies!   View Related
Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

View Replies!   View Related
Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6

View Replies!   View Related
Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire

View Replies!   View Related
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.

To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.

Is There any formula to show fail subject in Remarks column without creating 5 additional columns?

View Replies!   View Related
Copy A Formula In A Cell And Then Paste Only The Text Of The Formula
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.

When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.


View Replies!   View Related
Formula Arrays That Will Omit Empty Cells In My Formula
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.

View Replies!   View Related
Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

View Replies!   View Related
Not Responding: Added A Formula To A Spreadsheet With Som Complicated Formula
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.

View Replies!   View Related
Link Formula / Update Formula Automatically
Iím working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.

So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).

Iím wondering if itís possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).

I can copy the formula with the ďPast SpecialĒ function but the function is not updating if I change the formula on the main sheet.

So is it possible to link it and that itíll be updated automatically when Iím changing the formula on the main sheet?

View Replies!   View Related
SUMIF Formula That Is Collects Values From An IF Formula
Can anyone tell me why this formula is not working?

=SUMIF($D$6:$AL$6,"=TM Goals",D12:AL12)

The entry under cell T12, which I am hoping this formula will pick up is based on the following formula.

=IF(E12="A",$L$5,"0")

View Replies!   View Related
Formula Cell Showing Formula Not Result
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.

View Replies!   View Related
Formula's Keep Disappearing- Formula Does The Calculation And Then Disappears
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...

however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!

one such formula is:

=IF(AND(AC11"No Calc",AC11>=1,AC11

View Replies!   View Related
Use The Results Of A Formula As Column/row Numbers In Another Formula
I have two cells. The first cell has the formula:

=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))

which results in a col and row number (such as D65).

The second cell has the following formula:

=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2)
^^
I wish to replace the 'D1" in the Match function with the results of the first cell's formula.

I assume Indirect would work, but I don't know how to code the formula to use it.

View Replies!   View Related
Cells Display Formula, Not The Results Of The Formula
To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?

Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))

View Replies!   View Related
Formula To Determine If A Cell Has A Formula Or Constant
In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.

Examples:
=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".

View Replies!   View Related
A Formula Which Is Calculate 4-5 Dates Formula In One Cell
i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.

View Replies!   View Related
Logical Formula: Value If True/false To Be Another Formula, Not Just A Set Value
I want to use the If formula but want the value if true/false to be another formula, not just a set value. Here's what I came up with (obviously wrong, please excuse my lack of experience) =IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")

View Replies!   View Related
IF Formula Assistance: Formula That Looks At 2 Adjacent Cells
I need to create a formula that looks at 2 adjacent cells. If both cells show 'YES' then I need the 3 cell to show 'YES'. If either/both of the 2 adjacent cells are blank then the third needs to be blank.

View Replies!   View Related
Offset Formula: Getting N/A In Cell When Enter Formula
I have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab.

Question #1 how this formula works or what it does.

Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.

View Replies!   View Related
Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?

View Replies!   View Related
If(iserror Formula): Used A Variant Of This Formula Before Without Encountering
I'm going wrong with this formula: =if(iserror(J3-VLOOKUP (H3,$H$730:$J$1456,3,FALSE)," "(J3-VLOOKUP(H3,$H$730:$J$1456,3,FALSE)). I feel like I've used a variant of this formula before without encountering any problems.

View Replies!   View Related
Allow Input Into Formula Cell While Retaining Formula
i'm having trouble formating a cell to accept either user input or a equation i've made. i would like it to accept the user input and when there is no input to use the equation i have.

View Replies!   View Related
Can A Macro Write A Formula As An Array Formula
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?

View Replies!   View Related
IF Formula Does Not Work On The Last Part Of This Formula
This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.

=IF(E8>0,"$0.00",IF(E9199,(E9*0.01),IF(E10>50,"$50.00",0))))

View Replies!   View Related
Code Formula So The Formula Is Not In The Cell
I am using the Networkdays function to calculate the number of working days between a date entered in A1 and a date in B2 and I have a range of holidays called 'holidays' and then have the result placed in E1.

View Replies!   View Related
Insert The Rounddown Formula Into This Formula
I use this formula for converting date of birth to a number based on todayís date. I have tried to have the number.. 6yrs 6 months to print only 6 not 7 years old. How do I insert the rounddown formula into this formula

=IF((AD37=""),"",YEAR(AQ36)-YEAR(AD37))


View Replies!   View Related
How To Use A Text Formula In A Real Formula
This is going to be hard to explain but, ill give it a try

I have a list of formula written in text in columnB
(-0.0045*x^3+0.2696*x^2-6.0587*x+100)/100
(-0.0045*(x*1.3)^3+0.2696*(x*1.3)^2-6.0587*(x*1.3)+100)/100
(-0.0045*(x*1.5)^3+0.2696*(x*1.5)^2-6.0587*(x*1.5)+100)/100
(-0.0045*(x*0.8)^3+0.2696*(x*0.8)^2-6.0587*(x*0.8)+100)/100
(-0.0045*(x*1.3)^3+0.2696*(x*1.3)^2-6.0587*(x*1.3)+100)/100
(-0.0045*(x*1.5)^3+0.2696*(x*1.5)^2-6.0587*(x*1.5)+100)/100

each formula correspond to a type of road in columnA
Rural Local
Rural Collector
Rural Arterial
Urban Local
Urban Collector
Urban Arterial

I would like to create a formula that will choose the right formula and substitute the variable "x" by a specific cell (lets put $Z$1) to finally give me the final answer in column C.

View Replies!   View Related
Percentage With Formula Or Conditional Formula
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:

For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.

What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.

Therefore, I came to the conclusion that:
0.25% x 80 = 20 hours
0.50% x 80 = 40 hours
0.75% x 80 = 60 hours

Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.

View Replies!   View Related
DSUM Formula Instead SUMIF Formula
I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example.
I like to know is there any advantage of using DSUM instead of SUMIF in this example.
The formule they used is =DSUM(J109:K113,2,$M$2:$M$3)
and the criteria is G,D
so they are adding all Debits in that column.
I changed the formula to =SUMIF($J$110:$J$113,"D",$K$110:$K$113)
that of course does not need $M$2:$M$3 range criteria.

G 3458224.37
D 3401317.50
C 3401317.50
C 56906.87
D 56906.87


View Replies!   View Related
Running A Formula, Against The Output Of Another Formula
Need to run formula against the output of another formula, but excel is giving me an error because its seeing the formula not the output. Is there a way make excel run the formula against the output?

=IF(B1=1,AND(E1<=5,"WITHIN","NEEDS UPDATE"))

the formula on E1 is =DAYS360(C6,D6,TRUE)

View Replies!   View Related
Translate Mathematical Formula To Formula
In cell C2 I want to compute the value of 5x^6 + SQRT of 6, where the value of x is located in cell A%. What formula would I need to enter into cell C2 to do this?

View Replies!   View Related
Copy Formula (as Written In Formula Bar)
I use a macro to fill data from different files (in the same folder) to a master sheet. One particular cell (C16) of the files contains a formula for calculating Young's modulus (E) based on temperature (entered in cell C14). The same cell in all files has the same formula but since the calculations are based on temperature the value changes. In once case the formula is like:

View Replies!   View Related
Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.

View Replies!   View Related
Weekday Formula: When I Change The Month, It Automatically Will Change My Formula
=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

B1 = Jan
C1 = 2006

View Replies!   View Related
Status Formula (find Out The Formula Where Numerical Valued Between 1-2000 Equals True And Numerical Under 0 Equals False)
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.

View Replies!   View Related
Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View Replies!   View Related
Any Formula To Recognize Value Vs. Formula
Sounds kind of confusing, but here's what I am trying to do. I have a stock template that goes out to MSN Stock Quote and retrieves data each time you click a button embedded in the worksheet. After doing this, the user must click a "FINALIZE" button that pastes the values of what was just retrieved into the same cells, thereby eliminating the formulas. This part works fine. The problem is I want to create some sort of warning sign for the users that lets them know if they have left formulas in the spreadsheet before they send it to others.

Is there some sort of formula (kind of like an =ISERROR formula) that would recognize if a cell contains a value or a formula?


View Replies!   View Related
Formula From A Longer Formula
I took this formula from a longer formula. For some reason it is not working. I have the same amount of left '(' as I do right ')'. What is wrong with this formula?

=SUM(IF('DATA'!$G$2:$G$50000>="20030701",IF('DATA'!$G$2:$G$50000

View Replies!   View Related
Formula To ID Cells Containing A Formula
I need to identify cells that contain a formula so I can use conditional formatting to colour those without formula.

Most cells in my array contain a formula but occasionally I have to over write the result or enter a result manually because a vlookup is returning an error.

View Replies!   View Related
Formula - Quarter Formula
I have data in a spreadsheet that I have to analyze by quarter and currently the users enter Q1, Q2, respectively. I would like to eliminate this field, but the analysis formulas do need to break the data down by quarter. Does anyone know of a formula that finds quarters by date?

View Replies!   View Related
Do The Formula, And If F7 Has Data To Do The Formula
=IF(E7=F7,0,IF(E7>F7,E7-F7-INT((WEEKDAY(F7,2)+E7-F7)/7),F7-E7-INT((WEEKDAY(E7,2)+F7-E7)/7))).

I have this formula in a cell. f7 is the control cell. if f7 has nothing in it i dont want to do the formula, and if f7 has data to do the formula, is this possible to do?

View Replies!   View Related
Keeping Old Formula Within New Formula
Excel 2007. Creating an address book. Used a costume formula to return birth dates typed in without a slash (/) e.g. 163 as birth date with / (e.g. 1/63). This worked fine.

Want to combine the name of person (one column) with the birth date column (with /) in a new column so it reads: Jennie (1/63). (This new column combines name and birth date together, but also adds the parentheses to the birth date).

I can't figure out how to do it. When I use "&" (instead of concatenate) and substitute 2 columns range of cells I receive Jennie (163) (birth date without slash). Formula I'm using is: =F6:F134&" ("&G6:G134&") ".

View Replies!   View Related
IF Date Formula: IF Formula, That States If A Date Is More Than A Month After The Date In The Cell Then It Is Timely
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.

View Replies!   View Related
How Do I Use A "live" Formula Result In Another Formula?
In my example H3 is my problem. How can I use the "live" result of H3 to vlookup against my table of Part # A and Part # B in cell I 3?

View Replies!   View Related
IF Formulas (get The "then" Part Of The If Formula To Be Another Formula)
Is there a way to get the "then" part of the if formula to be another formula? I have the following formula which isn't working.

View Replies!   View Related
Change Formula To The Results Of The Formula For Cells That Have Results
I have a range that has formulas that are based on other fcells outside the range.

What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.

There could be a marco to run when this process is needed.

example attached

View Replies!   View Related
Adding Formula To Cell Using VBA Which Has "$" In Formula
Problem: This is what i want to show up in a given cell.

View Replies!   View Related
Formula To Target Another Cell: Formula/Data In Same Cell
Note: I know the syntax below is not how you would enter forumlas into Excel but I am using it to quickly illustrate what I'm trying to do and need help with. A cell not in column D, E, or F contains a formula of the form: F=IF(D>0, D/E, "blank") .
Note: If a cell in column D>0 (eg. D5>0) then the cell to the right of the selected D cell (eg. E5) will also be >0 ; otherwise, both cells will be empty.

This is what I want to do: If cell D?>0 , then F?=D?/E? ; otherwise, F?=empty .

Example 1: If D5>0, then F5=D5/E5 ; otherwise F5=empty .
Example 2: If D7>0, then F7=D7/E7 ; otherwise F7=empty .
Etc. I want this to apply to all rows.

I cannot enter the formula directly into the F? cell because sometimes I will need to enter data into cell F? manually. When I need to enter data manually into F? this results in the formula being overwritten by the new data and this means that if I ever needed to have data calulated based on the formula F=IF(D>0, D/E, "blank") I would have to re-enter the formula from scratch over again; obviously this is a nusance.

View Replies!   View Related
Copy A Formula Across Several Work Sheet And Have The Formula Always Take Data From Previous Work Sheet
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.

2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations

View Replies!   View Related
I Want To Be Able To Take The Solution To The Formula And "fit It" Into Another Formula.
=IF(AC4509="","",IF(W4509=1,ROUND(AR5+(0*(9.693/C4509)),1),ROUND(AR5+(X4509*(9.693/C4509)),1)))

If the value of W4509 is equal to 1, the solution would be whatever is dictated, otherwise, it uses the alternative formula to achieve the answer.

My question: I want to be able to take the solution to the formula and "fit it" into another formula.

Example: ((the "If W4509=1" formula)-88)*(C4509*.09693)+100-0) and ((2nd formula)-88)*(C4509*.09693)+100-X4509)

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved