I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.
View 9 Replies
I have an excel sheet which has been exported from an 'Employee Attendance Software',
http://www.flickr.com/photos/34247990@N06/4299288997/
In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.
Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion
http://www.flickr.com/photos/34247990@N06/4299288959/
I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100.
IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED
IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37%
IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52%
IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87%
IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07%
Is there a =LOOKUP formula that can be used here?
I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.
View 2 Replies View RelatedAll the answers I can find for this address either the auto-calculation setting or formatting the cell as a number. I am using this same formula in a bunch of cells to calculate totals: =SUM(E12:E17). It works find all over the sheet, except in one box. There is no difference in formatting or data type or anything.
Not working: notworkingexcel.png
Working: workexcel.png
The numbers being summed from the cells above the one in question are referenced from another sheet if that is relevant.
I am wondering if there is a way to return a cell reference by having excel
look at a table with certain parameters. For instance...
If there were a table with repeated dates such as:
Feb 12 1500 1530
Feb 12 1745 1750
Feb 13 1215 1245
Feb 13 1610 1700
Feb 13 1850 1900
Feb 14 1050 1140
Is there a way to return the cell number (ie A6) for the first instance of
Feb 14?
The formula should take the info from validation lists in R13 & S13, and match them up in F3:O3 (T1Q2) & C6:C15 (T2Q2) and return the data in the intersecting cell into R9 (Where the formula is kept). In this instance, it should find 1 at I3 and the 4 at C15 and return NN to box R9.
If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?
This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)
I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))
I am trying to trigger a formula if a cell (B2) is populated (with anything) but if it is blank for the destination cell to remain blank.
The formula I am trying to trigger is =IF(G18=0,100,100-(100/(1+H18))).
I am trying to find a formula for a vlookup that will check one cell for any value but if that cell has no entry then it will check a different cell to return the value how do i do this?
TEAMCSS Wetherill ParkCT NAME
above is where i need it to look CSS wetherill Park is cell C1 the cell that has no entry is H1, both of these cells have validation lists in them,
OS windows XP excel 2007
=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)
Is it possible to have a formula that will return the answer as the value.
For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.
I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.
Would this require a macro?
my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem.
I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:
I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1
Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day.
Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....
I'm looking for a VBA solution that will look at the machine name above in blue and insert in in column E
Machine 2's expected results have been filled in....
I have the new version of excel and I can't figure the correct formula to do the following.
Attached is my example.
A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1
Example
So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.
I am using HLOOKUP to show a date depending on the month shown in B10.
If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".
Here is my formula:
=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:
I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).
Here is my existing Keyword highlighting
Sub Highlight_Keywords()
Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long
My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!
View 3 Replies View RelatedI have the attached table. I need to format with conditional formatting with the following criteria:
1) If >= 80% , then flag as green
2) If >=1% but <80%, then flag as yellow
3) If 0%, then flag as red.
I did the formatting in conditional formatting but the it did not results in the desired solution.
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:
http://www.excelforum.com/excel-gene...-new-post.html
I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
I need to determine federal tax withholding from gross pay.
If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%
If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?
I tried this one: =IF(M10
I have two columns of adjacent data Call them A & B (although this could differ)
If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.
Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View 7 Replies View RelatedIt works I1 is blank and it works when F7:K7 has data and I1 is initialed. However, when I1 is initaled and there is no data in F7:K7 the cell reads "N/A". It should read "Approved with insufficient data".
=IF(ISBLANK($I$1),"",IF(ISBLANK(F7&G7&H7&I7&J7&K7),"Approved with insufficient data",LOOKUP(99999999999,F7:K7)))
I am trying to sum a column with values using a report downloaded from Celltracker, however the sum total is returning as zero.
When added using =a1+a2 the value returns correctly.
I need a formula for cell P5 that returns the date for the following Wednesday from a date in cell I3?
For example, if in cell I3 01/05/2014 (through to 06/05/2014) is entered, cell P5 should show 07/05/2014
If the Wednesday date is in cell I3, cell P5 should should that same date (e.g. 07/05/2014 in cell I3 and P5).
I am using this formula. How can I get it to pull 'Sales Materials'!$F17:$F69' and display along with 'Sales Materials'!$B$17:$B$69 when my logical statement is true.
=INDEX('Sales Materials'!$B$17:$F$69,SMALL(IF('Sales Materials'!$E$17:$E$69="YES",ROW('Sales Materials'!$B$17:$B$69)-16),ROW(A1)),1)
I've tried using * but I think something is going on with "-16" which I was told to you use to account for headers.
=AVERAGEIFS($D$106:$D$2825,$D$106:$D$2825,">"&$I117,$D$106:$D$2825,"<"&$J117)
Returns = 0
But on a copy of the same worksheet, the same formula returns the correct value. Is there something wrong with my formula cotext?
