Between Formula: Give A Whole Number When A Certain Criteria Is Met

Jun 8, 2007

I am trying to do something for a baseball game I am creating. What I am looking for is a formula for one cell that will basically give me a whole number when a certain criteria is met. I am also looking for this cell's formula to get part of it's formula from another cell in the document. So let's use cell A1 has 1.50 in it.
I want this other cells formula to be able to give me a 9 if A1 is 0.00-1.50, a 8 if A1 is 1.51-2.79, 7 if A1 is 2.80-4.30, a 6 if A1 is 4.31-5.49 and 5 if A1 is 5.50-500.00.

View 6 Replies


ADVERTISEMENT

Formula For IF There Is A Number Present Then Give Minimum Or Maximum Value?

Feb 12, 2014

I need a formula to give me a MIN or MAX value only if there is a number greater than 0 in the cell AND not alphabetical letters (as some cells do contain). I need it to return the value for that cell across about ten sheets--I have that part figured out (I think). But I keep getting a #Ref error.

View 9 Replies View Related

Formula To Look At All 9 Data Points And Give The Place Number

Jan 19, 2009

Column P is the percentage of body weight lost...

P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%

What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.

View 2 Replies View Related

Index Matching: Formula To Give "name" Associated With The Largest Number In A List

Nov 10, 2009

INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))

I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.

View 5 Replies View Related

Performing IF Cell Contains Number THEN Copy That Number ELSE Give 0

Jun 14, 2013

Looking for a way to search an active cell (that has been found using macro) for numerical data and if there is some present paste this in a different location

If there is no numerical data in this this cell then paste a 0 in a different location

An example of the string contained in the cell

Product 7.5% wheat <- i would want 7.5 (or 7.5%) copied to a different cell
or
Product X <- i would want a 0 copied to the cell the 7.5 would be in from the above

I can find the cell to search in, i just do not know the correct way to extract the information I need.

View 9 Replies View Related

Spreadsheet That Will Give A Total Number For Each

Aug 7, 2009

I have a table with 5 columns and approx. 85-90 rows.

Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total)
Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue
Column C has Session Type - Booking, Drop-In
Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used
Column E is not needed

I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator.

I am thinking of creating a new sheet with a number of cells that have a formula similar to this

=IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0)

But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.

View 5 Replies View Related

Column() - Give Letter Instead Of Number?

Nov 27, 2007

When using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?

View 9 Replies View Related

Give Cell Number After Letter With Function.

Dec 3, 2009

I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

View 4 Replies View Related

Automatically Give Number Of Hours Worked

Feb 6, 2007

I'm working with a spreadsheet that works out how many hours someone has worked on a given shift.
In cell C4 I have the formula =E4-D4+IF(D4>E4,1)
In cells D4 & D5 I have an entered number in time format (custom hh:mm)
The problem with this is that when a time is entered in either D4 or E4 it automatically gives me a number of hours worked (upto midnight) without any input in the other.

Aim: Not to show the number of hours worked in cell C4 until hours in both cells D4 & E4 have been entered. NB. Sometimes the start times are 20:00 until 08:00 (night shift) hence answer 10:00.

View 9 Replies View Related

Give A Valor To A Selected Number In A Drop Down List

May 25, 2007

when i selelct a number of a the dropdown list can i get it to show other number in the cell below? example if i select 7:00-15:00 from that list it would show 8:00 in the cell below..

View 9 Replies View Related

Excel 2003 :: Get Give Each Row Inputted Unique Number For Later Editing?

Mar 14, 2014

I have created a workbook to input, store and part edit defects on plants and machinery by userforms. I have everything working except either picking up a number I have pre-loaded into a column or auto generating a number onto the inputting userform. I need a number in the userform to be either generated in the next empty row in column I or taken from the numbers in the next empty row I have loaded into column I. Password is snatch.

Input 2003 test1.xls

View 3 Replies View Related

Formula That Calculates AVG Number Based On Exact Criteria

Jan 14, 2014

Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?

Please see an example:

Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer

[Code] .....

I guess it has something to do with AVG and IF formulas.

View 2 Replies View Related

Formula To Count Number Of Cells That Meet Criteria?

Dec 18, 2011

I need to formula to count the number of cells that meet the criteria below,

Find the letters 'AT' in some part of the cell and a blank cell next to it...

Doing a different formula for each, doesn't bring out the correct figure.

View 3 Replies View Related

Formula- Return Number Of Aged Breaks Under Each Criteria

Jun 30, 2009

I need a formula that will return number of aged breaks under each criteria in sheet 1 row 1 and each team based on the unique case number, so for example in sheet 2 I have my raw data starting in A1, i should get the below results in sheet1 ...

View 9 Replies View Related

Find Largest Number In Array And Give Result As Column Heading

Aug 9, 2013

I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below

Red
Green
Blue
Orange
Yellow
Result

2
4
3
6
1

[code]...

I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers

View 4 Replies View Related

Parenthesis - Formula Give An Answer Of 178

Oct 27, 2005

The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.

The answer I get is 308669

=(((F22*71449)*0.4)+16147/C22)

Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8

View 10 Replies View Related

Formula: Give Highest Dollar Value

Apr 8, 2009

I have 6 rows (A-F) with dollar vallues. I am trying to create a formula on row G that will give me the highest dollar value out of rows A-F.

View 9 Replies View Related

Formula That Give Figure Against The Name In Column

Jun 30, 2009

I’m looking for a formula that will give me a figure against the name in column A (name) while looking picking out a specific word within the column B, say "alarm"

So it reads
Alan Ingram 1
David walker 3
And so on...

View 9 Replies View Related

Formula That Counts Number Of Unique Text Values If Meets Criteria

Nov 6, 2013

I have been using this function to count the number of unique text values in a data set:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

View 7 Replies View Related

Forumla To Give A Starting Number And Take One Away Each Time A Code Or Codes Are Dislayed In A Cell Range

Mar 22, 2009

In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this

A1 = 23 days or any number days i needed

Cell range B1:C52

every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD

View 4 Replies View Related

Lookup Formula That Will Give Multiple Items?

Jan 13, 2014

Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells

View 4 Replies View Related

Need Lookup Formula To Give Column Range

Jul 26, 2014

I'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."

I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.

Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.

View 8 Replies View Related

Formula To Give A Time Minus 15 Mins

Aug 1, 2008

i have =TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2),0). which gives a time in the format 9:30PM, or 10:30AM etc. How would i change the formula to give me a time minus 15 mins. Eg, if the above formula gave me 8:45PM, i would want it to show 8:30PM

View 4 Replies View Related

Formula To Compare Cells And Give Output As Txt

Jan 21, 2010

I know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:

lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".

I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.

View 14 Replies View Related

Formula: Give User Limited Control

Jan 26, 2010

1. The example is cell B1 has a drop down list with the following options: MIN, MAX, AVERAGE, MEAN, SUM

The range F2:F20 hold the values upon which I want the formula to work

Cell B3 is to calculate the result of performing the the function defined in B1 over the data set in F2:F20

What formula do I need in B1. I have tried various of methods of forming the above into a text string and then preceding with INDIRECT but my problem seems to be with the range part.

2. Same as above but I want to the users to write as text (without equal sign) the formula they want to use

View 9 Replies View Related

Write Formula To Give Result If X Is Greater Than Y And Z?

Aug 7, 2012

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 Related

Formula To Be Able To Give The Full Time Rate

Oct 5, 2009

I have a spreadsheet, but I need the following formula to make our lives easier here.

I have the following columns:

Rate, I/T/P/S, FT/PT

I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94

View 9 Replies View Related

Excel Formula To Compare Text And Give Result

May 5, 2014

I have multiple sheets in excel file. Every sheet having same information for different areas. e.g. The bug was reported. One sheet might have answer in YES and other sheet might have No. I need to get answer YES if even one of sheet contains YES for the field.

View 9 Replies View Related

Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

View 2 Replies View Related

Why Counif Formula And Filter Option Not Give Same Results On Excel

Aug 24, 2012

I am using countif formula to count number of times each name appears in a row, but when I use the filter opeion and select a specific name, and highlight the whole row, the 'count' at bottom right of excel screen does not show same count as the outcome of countif. why is this so?Shouldnt they both be the same?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved