I have this table with min and max amounts that requires a fixed amount when when the condition is met. How do I write a formula for this. If result is >$0 but < $100 = $15 and so on. I canlt get it right..........
View 3 RepliesI have the following values that I need to perform a calculation on, but I am not sure how.
A1 = -.98
A2 = .98
A3 = 1.0
I would like the results in colmun B to be:
B1 = 1.02
B2 = .98
B3 = 1.0
I would like to achieve this with a formula that I could fill down.
I need a formula, If the number is greater than 1 and less then 30, it returns Yes, if not, then no.
Need the formula to find any number from a range under this format 00:00:00, Greater than 04:00:00 but Less Than 12:00:00.
View 5 Replies View RelatedBelow is my formula that works for a specific date. F$8 is the date.
How could I change this if I want data b/w two time periods.
>=F$6, and <=F$8?
[Code] ....
I need to display two separate values from a given input, but not exceed a specified number for that cell. I have this so far except for the maximum number that can be displayed; Example for what I want below ....
View 8 Replies View RelatedColumn B is basically 550 points + column C = Base Roll.
We need to introduce a cap so no more than 1000 points can be displayed/earned. So column G adds all points & column F caps.
So far it's all ok.
Now when i add a value to column E (points spent) those points are deducted from column G instead of the capped 1000.
Entering a value of 500 displays 800 in column F instead of 500.
I'm trying to find a formula to use for a specific numerical outcome. For example say you have two teams playing throughout the year and you've made a prediciton on the results. If your prediction is correct your awarded 7 points if you predict a win its 3 points and if your incorrect than it's zero. what is the formula for awarding points?
I've attached the spread sheet : FIFA Spread sheet.xlsx
When grading children's test scores I want to apply letters and numerals to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help with a formula.
View 14 Replies View RelatedCurrently I am using the following formula ....
View 9 Replies View RelatedI have a range of weekly sub totals that get entered each week for the year.
o8:bn8
As each week are entered I am trying to find the lowest week's production (using the =min formula) that is above 0 (weeks not yet entered appear as 0) the problem is that it keeps defaulting to the next column once a number greater than o is entered.
I need a formula that looks at the total in H40 and if the number is between 32 and 40 I need it to return the number then if the number exceds forty I need to multiply the overage by 1.5 and add it to the 8 for a total of 11.
I think it would be something like:
SUMIf(H40>"32",
I have a time value h:mm in one cell and I have a range of three cells that I want to display values depending if the time is:
under 12 hours
over 12 hours and less than 24 hours
over 24 hours and less than 36 hours
over 36 hours
I have been using the following formulas where A1 holds the time value:
=IF(A1<12,"true",0)
=IF(24>A1>12,"true",0)
=IF(24>A1<36,"true",0)
=IF(A1>36,"true",0)
None of which seem to work
NumberABC D Value to be returned
1 -10050 0 C
2 156.6700-31.34A
3 104.5800-20.92A
4 -6.2000 #NA
5 0 -3500 #NA
6 132.800-33.2 A
7 0 1000-50 B
What formula i need to apply so that i get values which are in column "Value to be returned"
formula stating that:
if a number is greater than 1 add one 1 box (250) and if the number is greater than 9 add 2 boxes and is its greater than 18 add 4 boxes
i want this all in one formula,
I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.
Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.
I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.
I need a formula that will divide a number and if the answer is greater than a 100 then blank.
i've been using this =IFERROR(SUM(H4/C4,"",IF(K4>100,""))) but no luck.
I'm getting a mismatch error on my If activecell.value > 5 Then portion of this code. I'm trying to say if the value in column AG is greater than 5 then create an email address from the first and last name. I've even made sure to copy and paste the formula results as values.
Code:
Sub CheckPFPDates()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim emails As String
Dim salution As String
Dim currenttime As Integer
[Code] ........
If the total is >= to x but =(I10*0.9),"Over 90% spent",IF((M10+T10+AA10+AH10+AO10+AV10+BC10+BJ10)>=(I10*0.8)&(M10+T10+AA10+AH10+AO10+AV10+BC10+BJ10)
View 3 Replies View RelatedHow do I use the SUMIFS formula for ranges greater/less than some number?
In my spreadsheet I need to sum the values that are less or equal to January 1st, for example. How do I do it? Because this formula allow to find values equals to something.
I have this formula
=IF('Senate 1'!K10="",'Senate 1'!K$8,IF(S10>'Senate 1'!K10,S10,""))
what am hoping i can get it to perform is have S10 replace it self each time a greater value is inserted in Cell S10.
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount
5-10 items they receive 2% discount
11-20 items they receive 5% discount
21-50 items they receive 8% discount
over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
Formula to pull the lowest number that falls in Cells I2:L2 and the number must be 0 or Greater. Some of the numbers might be negatives and I am trying not to pull those numbers.
View 3 Replies View RelatedI am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.
We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)
My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?
Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00
If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2
My formula is =IF((C2+E2)>D2,"WIN","LOSE") the new formula if I wanted the cells with "WIN" to be green, and the 'LOSE' cells to be yellow. Excel 2010
View 6 Replies View RelatedCurrently, I am using countifs to count data within a specific range if it meets a criteria. Now I want to add to the formula. I need to count the cells within the specific range meeting the existing criteria I already have but only if one cell is greater than another cell.
This is what I currently have: =COUNTIFS(Report!$F$4:$F$1048576,">=8/1/2013",Report!$F$4:$F$1048576,"=8/1/2013",Report!$F$4:$F$1048576,"
SUMIFS with less than & more than
I found this thread which works for normal ranges
I want to Sum for Values Greater than a specific date in my formula.
This first one works for a specific date (Relevant reference in red), so say 01/01/2014
Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
Code:
To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
I have a Macro of 55 Columns and 2000 Rows I need to change the value in several Cells of the row in which the value of AZ is 200.01 or more. I need the following if ANY Cell in AZ2:AZ2000 is equal or greater than 200.01 then the following Cell in that Row will equal the following:
AG = 20
AW = 11
AX = " " (BLANK)
BC = N
I have attached an example of the spread sheet with Macro embeded and how it should look after the above is run. I do not know if or how to I need to tag the macro within the attachement.
I'm struggling to complete this formula. No matter what the entry in M30 is
(which is a concatenated formula from another sheet), I only get the highest response, which is 18.
I'm assigning a risk score based on a dollar amount. The formula is: ....
This is what I am tyring to do. I would like B7 to show $60 once A1 is greater then 25.
View 3 Replies View Related