I have created a simple SUM formula to add cell values across six sheets in the same workbook. I see the result when I use the formula button but the result (number) does not appear in the cell. All I get is $ -.
View 9 RepliesI 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
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedI am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
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," "))
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.
I would like a formula that looks for Y and results Y. See two examples below.
Example
Cell B2 = Y
Cell D2 = N
Cell E2 = Y
Cell F2 = Y
Example
Cell B3 = N
Cell D3 = N
Cell E3 = N
Cell F3 = N
what was the anser to a simple question earlier has now got me thinking...
I have one mega formula (see below) that based on a set of criteria returns a result from a range in an accompanying workbook:
=INDEX('input plus'!D940:D979,MATCH(1,('input plus'!$DJ$940:$DJ$979>=2)*('input plus'!$DG$940:$DG$979=1),0))
(the range 940:979 is a filled with investment funds data all within a particular sector, this formula for example will return the name of the number 1 rated fund, ie "mega star cash")
However, the above assumes that the ranges specified wont change, but they do, frequently and there are over 300 of them, so I earlier got help to create the following formula which can tell me what range a certain set of data lies within:
=CONCATENATE(ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,0)+0,4,4,1),":",ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,1)+0,4,4,1))
(so I tell this formula to look at a specified sector name 'cash funds' and by tweaking the number in bold it will return the range of data that i want to feed into the first formula, ie it will return D940:D979, DJ940:DJ979 and DG940:DG979)
If the data ranges change then these cells will dynamicaly update, either the range will shrink or grow - which is perfect - what i want to know is if i can integrate this into the first formula, so if you like it becomes completely dynamic.
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 3 Replies View RelatedI 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.
I have a spreadsheet which matches Reference numbers in Column A against Column B and vice versa; and assigns unique or duplicate values. I would like to be able to count the number of duplicates and uniques giving a total of how many appear in the spreadsheet. I've attached the spreadsheet to give you an idea of what its like.
View 3 Replies View RelatedHello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?
View 3 Replies View RelatedIs I have a VLOOKUP formula which results in a defined name- great! but when I reference that result in another formula I get a #REF error, if I type the defined name directly in to the formula (index btw) all works but I need the formula to recognise the referenced cell.
Example
=VLOOKUP(B8,Cable.Table,2) this results in a defined name of a table (D1A) in cell B30
The cell B30 is named "table". I then have another formula in B33 which is:
=index(B30,B47,C12) or =index(table,B47,C12) Which gives me the #REF error
If I input =index(D1A,B47,C12) the function works but it needs to be the initial reference from the result of the other formula and recognise that it is a defined name.
In A1 I have this: $9.50.
In A2 I want to display the result of multiplying A1 with 10% and subtracting the result from A1.
I've tried this in A2: =SUM(A1*10%)-SUM(A1)
But it displays a negative number: -$8.55
I need it to display: $8.55
The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.
******** ******************** ************************************************************************>Microsoft Excel - PLAY 4 EVE MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1F2G2F3G3F4G4F5G5F6G6=ABCDEFG111/10/0966666666Q211/09/0977667766DD311/10/0977767776T411/11/0972467246S511/12/0977667766DD611/13/0978667866DSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Is there any way that I can apply the roundup feature to an entire workbook without having to do each cell individually?
I have 4 source workbooks and a master workbook which is linked to the source workbooks, if the source workbooks have no value in specfic cells the master workbook which is linked to it shows a 0 (not a problem). Now if I want to copy only cells which have a value other than the 0 to the end of used rows on another sheet on the Master workbook using VB I run into a problem because it sees 0 as a value and copies as well.
The issue is how do I stop the 0 showing and not being seen as a value. This 0 shows regardless of format.
I have a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).
Read & Sign Tracking-061614.xlsx
I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.
As a example i have attached the sheet.
Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).
Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates
I created the following condition for each product code under each month in SHEET1
=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")
The formula only works for two product codes (A and B), and then yields blank cells.
how to take the result of a formula calculated in one cell and show the results in an other cell
View 4 Replies View Relatedi put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
View 4 Replies View RelatedI'm trying to enter relatively simple drug calculation formulas in Excel where the result is appended with the proper units to administer. For example, if I'm calculating for units of regular insulin with a formula such as:
=0.75*f2
I'd like the cell to make the above calculation, and append it with "Units Reg Insulin" or something similar. Thus far, I've just been putting the units as text in adjacent cells but the result looks a bit messy.
Is this possible? I'm using Excel 2007 and the Help index does not seem to offer much guidance about this.
I need to show sum and not concatenated values have a userform w 60 fields for numbers as follows:
a) UserForm1.TextBox34 to UserForm1.TextBox41: 8 fields-CAD currency
b) UserForm1.TextBox42 to UserForm1.TextBox49: 8 fields-USD currency
c) UserForm1.TextBox50 to UserForm1.TextBox57: 8 fields-exchange rate
d) UserForm1.TextBox58 to UserForm1.TextBox65: 8 fields-Total CAD currency => the formula here works across the board
UserForm1.TextBox58.value = Format(UserForm1.TextBox34.value+UserForm1.TextBox42.value*UserForm1.TextBox50.value, "###,##0.00")
e) UserForm1.TextBox66 to UserForm1.TextBox73: 8 fields-local taxes in CAD currency
f) UserForm1.TextBox74 to UserForm1.TextBox81: 8 fields-federal taxes in CAD currency
g) UserForm1.TextBox82 to UserForm1.TextBox89: 8 fields-total for first column in CAD => the formulas here works as well across the board
UserForm1.TextBox82.value = Format(UserForm1.TextBox34.value + UserForm1.TextBox42.value * UserForm1.TextBox50.value + UserForm1.TextBox66.value + UserForm1.TextBox74.value, "###,##0.00")
have a grand total formula pulling in all values, and this one works as well; so i have 4 simple addition formulas, and the local & fed tax formulas do not.
however, the formulas for the sub-total for the local taxes and the federal taxes do not work; the results show a concatenation of the 8 values rather thn the sum of the 8 values; the formula for the local tax is as follows:
UserForm1.TextBox110.Value = Format(UserForm1.TextBox66.Value + UserForm1.TextBox67.Value + UserForm1.TextBox68.Value + UserForm1.TextBox69.Value + UserForm1.TextBox70.Value + UserForm1.TextBox71.Value + UserForm1.TextBox72.Value + UserForm1.TextBox73.Value, "0.00")
I have rewritten the formulas and still these two tax formulas result in concatenated numbers rather than the sum.
I would have u/l worksheet but it's roughly 4MB now.
Ignoring Negative results in a formula
Question withdrawn
Just yesterday when I opened my excel file, all the cells that contains formulas does not display the results (leaving the cell blank).
For example the formula for cell A1 is =B5. But after putting a value in cell B5, cell A1 is still blank.
In Excel, what is the reason for different results using the two formulas below?
Example:
Cell L6: 1413.5
Cell M6: 1312
Using the formulas below, the format of the cell is Percentage, one decimal place but I get different results
Formula 1: =-(1-L6/M6) The result is 7.7% change
Formula 2: =SUM(L6-M6)/L6 The result is 7.2% change
What is the difference in the formulas?
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
I am trying to track the number of "F"s within a cell and I'm sure that I'm using the formula correctly -
=COUNTIF(I2:I29,"F").
When I use the insert function feature to walk through setting up the function step-by-step - I see the count once I type in the criteria - however, when I hit 'Ok' the count doesn't appear in the cell - the formula appears. I have cut and pasted the entire worksheet into a new spreadsheet, the problem remains.
I am using a vlookup formula to link 3 pieces of data together. I would like to insert a space between the 3 pieces. Currently formula result is:
item1item2item3
I would like it to view as:
item1 item2 item3
not sure how to alter the formula to create the space....