Trying to have a formula assign a number to a cell depending on a number in that cell.
Example
In all cells in a range from C8 to G13, if there is a 1, i want the number in C20 to show, if there is a 2, whats in
D20, 3/E20, 4/F20, 5/G20, 6/H20
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies View Relatedhow to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies View RelatedSo I have this list (I made it a little bit shorter).
So what you see is two different tasks (01 and 02) and three different conditions (A, B and C). In column B you see the result I would like to have. '/Searchtask_01.html' in A1 belongs to conditions A, because it is in session A. However, '/Searchtask_01.html' in A10 belongs to conditions B, because it is in session B.
How to get the results in B with a formula?
I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
View 1 Replies View RelatedI am looking for a formula which will insert a word into cell A3 depending on the number in cell A2. For example A2 is between:
0 - 10 then insert the word Div 1
11 -17 then insert the word Div 2
18 - 23 then insert the word Div 3
24 28 then insert the word Div 4
I have a spreadsheet with several columns that have percentages as a whole number and with a decimal format. I need assign a score value to the cell that contains a percentage depending on a range.
For example:
If cell A1 has a value between 97%-100% then A2 should display "15", however if A1 has a value of 90%-96% then it should display "12" and if A1 has a value of less than 89% then it should display "0".
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
SAMPLE FILE "error_finder.xlsx (36.5 KB)" attached...
The VP of our company needs me to create a spreadsheet.
For this s/s I need the following:
Imagine 2 sheets in Excel...
sheet1
sheet2
sheet2 has sequential numbers in column1 starting in row2 with the number 1 until wherever..., so cell A2 has number 1, cell A3 has number 2, etc...
Manually, a user has to fill in certain text next to a number.
This for example would look something like this:
_|A| B
-|-|-----------------------------------|
1| | Error-description
-|-|-----------------------------------|
2|1| Whatever the 1st error would be...|
-|-|-----------------------------------|
3|2| Whatever the 2nd error would be...|
-|-|-----------------------------------|
4|3| Whatever the 3rd error would be...|
-|-|-----------------------------------|
5|4|
-|-|-----------------------------------|
6|5|
-|-|-----------------------------------|
and so on...
All this would be on sheet2
In a cell on sheet1 I now need the number from column A displayed, that has the latest entry in column B.
In the example above this would have to be the number 3 in cell A4, because right next to it (in cell B4) is the last entry "Whatever the third error would be..."
Addition: There are several sheets, each sheet stands for one error listed on sheet1 in column A
sheet1 for example would look like this:
_|____A____|_B_|_C_|_D_|
-|---------|---|---|---|
1|_________|833|933|934|
-|---------|---|---|---|
2| error 1 |___|___|___|
-|---------|---|---|---|
3| error 2 |___|___|___|
-|---------|---|---|---|
4| error 3 |___|___| 3 |
-|---------|---|---|---|
In this example, because "whatever error" (cell B2 to B4 on sheet2) was found on press 934 was the 3rd error (cell A4), the number 3 (cell A4) would have to be populated on sheet1 in cell D4.
Cell A1 = 3
Cell A2 = 4
Is there a formula to calculate
(3*4)+(2*4)+(1*4)
I need to multiply every integer less than the number in cell A1 and greater than zero by the number cell in A2. I was thinking factorial, but that's not it... Can't remember from my math days.
I have numbers in b2 to b5 say
10
22
35
50
I have numbers in c2 to c5 say
1000
1580
3000
45000
I have numbers in cells d1 to kq1 (1 to 300) d1=1, e1=2, f1=3 and so on
What I want to do is:
In D2 through to kq2 it puts the number in to the cell that correspnds to a multiple of the number in b2.
That would put 1000 in cells m2,w2,ag2,aq2, ba2 and so on
In D3 through to kg3 it it puts the number in to the cell that corresponds to a multiple of the number in b3
that would put 1580 in cells y3,au3,bq3,cm3,di3 and so on
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View RelatedLooking 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.
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?
---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!
View 6 Replies View Relatedcan I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.
View 4 Replies View RelatedI have a list of numbers such as:
A01
A23
A53
A64
A74
A128
B01... the goes on too approximatly D128
and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53
is there any method that achieves this?
I'm trying to grab a number out a cell and use only the number for other formulas.
So in cell A1 I have 2 LK, in cell A2 4 LK, and in cell A3 is 1/2 LK. I want to just pull the 2, 4, and 1/2 out without the LK and put it in the B cells. I'm sure this is easy but I'm thinking slow tonight lol.
A
B
1
2 LK
2
4 LK
3
1/2 LK
I am wondering if it is possible to create a formula in a cell that will divide whatever number is entered into that cell by a set or fixed number.
ie. I enter the number 9 in the cell and when I press enter, the 9 is instantly divided by 15 and the answer is displayed in the cell where I just typed the 9.
I would like to get number of cells since the cell value exceeded a given number in a given range of numbers.
example
cell address cell value
a1 1
a2 1.9
a3 -1.2
a4 4
a5 3
I would like to know number of cells since value was less than 1 : the answer is 3 (a5,a4,a3)
Number of cells since number has exceeded 3: the answer is 2 cells back ie a5,a4
I'm trying to use the formula vlookup, but running into trouble. I'd like to use =VLOOKUP(3000&A1,E:F,2,0) but this isn't working correctly because it doesn't recognize "3000&A1" as a number? Is there a way to do this? This will be used in a VBA code, so I prefer the solution in VBA, but either way is fine. See attached for more info.
View 4 Replies View RelatedI need this for a tracking sheet of scores. For example, 1 gets 100 points, 2 gets 90 points, 3 gets 80 points, etc. I need to set it up for 10 places. I have no idea and have fiddled with it for two hours now. I need to be able to put a 1 in the cell and 100 appears after I hit enter, etc.
View 5 Replies View Relatedto update these values via a form in this sheet. I can find the correct row to be edited by entering a value from column A and B. The problem is if I want display the values of that row first and then change it. If I want to change row 10 data how can I bring back the value in ROW 3 AND THE COLUMN VALUE? The next step would be to do the actual update if I want to change ROW 10 to "Ooi" and a sales value of 200?
This is what I have done so far:
Dim myRows As Integer
With Sheets("Mrt")
'Retrieve history information for row
For myRows = 4 To 49
If comboxDay.Text = Range("A" & myRows).Value And textboxdescription.Text = Range("B" & myRows).Value Then
textboxbedrag.Text = Range("C" & myRows).Value
chkBTW_Ja.Value = Range("D" & myRows).Value
txtNota.Text = Range("S" & myRows).Value
End If
Next
End With
Picture attached to show how sheet looks like.
I currently have a spreadsheet which I use to do an audit of the office stationery supplies.
There 3 columns at the focus of this question:
F - Number of items in stock
H - Stock re-ordering level
I - Order Required
I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.
The statement is =IF(H1>=F1, "Yes","No")
This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.
Any value I enter in F will cause the I to say "Yes".
Is there any way to make the word "Special" equivalent to 0, so I says "No"?
In column 'o' I have inserted a number for course name in column 'p'. I want to assign a course name to that particular number as follows:
Column 'o' Number ... Column 'p' Course name
1: A. B. C.
2: J. K. L.
3: P. Q. R.
4: X. Y. Z.
And so on as per course names.
If I enter number 1 in inputbox then I want to enter the course name in column 'b4' respectively.
I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?
View 3 Replies View RelatedI have a medication start end and admission date.
and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.
I use my spreadsheet to keep score during tournaments. when i calculate my leaderboard at the end of the event i list the results first place all the way down until it finishes the list of players. i need to be able find players with the same or equal score and list them as tied for the same place.
another words if i get to the 3rd place and i had 3 players tie for third i need my leaderboard to show them all in 3rd place . currently it would number then 345. i listed below how it should format it. i also attached a file it will make it more clear.
We have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.
Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?
Sheet2 ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5 6DateDesired
Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412-
AprWeek2 1513-AprWeek2 1614-AprWeek2 1715-AprWeek2 1816-AprWeek3 1917-AprWeek3 2018-AprWeek3 2119-
[Code] .........
I'm currrently using column I and col J
in col I are all the units of measurement: KG, PKT, BAG
in ColJ is the formula to return:
KG as 9999999
PKT as 8888888
BAg as 7777777
if(i2=kg, 999999999,"false!")
i was thinking of using if....but triple if formulas would be long isn't it?
PART2 of this would be using:
=IF((SUMPRODUCT(--(db!$A$2:$A$36000=$B$1),--(db!$F$2:$F$36000=C$5),--(db!$G$2:$G$36000=C$6),--(db!$E$2:$E$36000=$A7),--(db!$D$2:$D$36000=$B7),db!$I$2:$I$36000))=9999999,"KG","Invalid")
to check 5 values and if it is 999999999 it'll return KG
8888888 PKT
7777777 BAg