How To Combine 2 Functions In One Cell

Jul 29, 2014

I've been struggling with the following problem for hours now and getting nowhere fast.

I've got '=(100/X6)*L6)' in cell M6 which gives my percentage fine but I need to round to the nearest integer with '=ROUND(M6,0)' Question is how do I combine both functions to provide the nearest whole number in M6 ??.

View 6 Replies


ADVERTISEMENT

Combine 2 Functions Into 1

May 27, 2009

I tried to do a search for this but the search function was not working.

I have an excel sheet that I can scan Mac address into. The reason I created the sheet is because when I scan them in they are uppercase and do not have separators. I would like to combine 2 excel functions into 1

This one

=LOWER(C2)

and this one

=LEFT(E2,2)&":"&MID(E2,3,2)&":"&MID(E2,5,2)&":"&MID(E2,7,2)&":"&MID(E2,9,2)&":"&MID(E2,11,2)

View 2 Replies View Related

Combine Functions Of 3 Buttons Into 1

Dec 16, 2008

In the attached xls I have a button on each tab, ideally I would like the button on the first tab to do everything, but I cannot seem to get it to work hence the current layout.

First button on first tab creates new xls and copies content of 2nd tab onto it, then copies various bits of info from certain cells in tab 2 into the 3rd tab.

2nd button on second tab simply clears the contents of the form, which is no longer needed as a seperate copy has just been created.

3rd button on 3rd tab just tidys up the cell borders

It would be much easier if I could get one button click to do everything (in that order preferably).

View 14 Replies View Related

Nest / Combine 2 Sumproduct Functions From 2 Different Tables

Aug 20, 2014

Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.

=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))

Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.

View 6 Replies View Related

Combine Iserror Vlookup And Left Functions

Oct 2, 2008

XL 2003

I want to combine these three functions

=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror

View 9 Replies View Related

Combine Or Nest Multiple Lookup Functions

Dec 16, 2006

I've got an indexmatch that works great
=IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))

But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.

I am trying to accomplish this:
IF B$20 (which is a dollar amount is = X, then index, match....
IF B$20 is > Y but < Z, then index, match...

View 9 Replies View Related

Combining Two Functions Into One Cell?

Jul 23, 2014

I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")

Here is a sample table with showing the data and result

4
5L
4,5L

5
6R
5,6R

5
7L
5,7L

6
10R
6,10R

6
3L
6,3L

View 1 Replies View Related

Complete Three Functions In One Cell

Mar 27, 2014

What would the formula be to complete all three of these functions in one cell?

64
-3
61

When positive & negative then G1+T1=X

-8
-8
0

When negative & negative then G2-T2=X

3
2
1

When positive & positive then G3-T3=X

View 6 Replies View Related

How To Make 2 If Functions In 1 Cell

May 20, 2008

I made a drop down list in cell H11 for Marital status (married,defacto,single,widow). Those are the 4 options in the drop down list.

Now im trying to make in another cell IF (H11="Married" OR "Defacto,950,false)
but wont work, i know im wrong but i learn by trial and error or last resort MREXCEL and m ytrial and error didnt work.

View 9 Replies View Related

Formula That Can Do 2 Different Functions In One Cell

Jun 17, 2006

I'm looking for a formula that can do 2 different functions in one cell. I have a subtotal in A1 and my total in B2. Now, I want B2 to say if A1 is lessequal to $100,000 then multiply by1.5% plus A1 and if greater than $100,000 muliply by 2.5% plus A1.

View 9 Replies View Related

Evaluate Multiple Functions In The 1 Cell

Oct 26, 2007

When using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula?

E.g. say you have:

= SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207")

And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part?

Im using Excel 2003 by the way.

View 5 Replies View Related

Pull Keywords From Cell Range And Combine In Single Cell

Aug 1, 2014

I am in need of a way to pull keywords listed in B2:B10 from text in A2:A10 and then those pulled keywords combined in cell C11.See attached document. So I have colors for keywords in column B and some random text in the column A including the keywords in parenthesis. Is there a way for a formula to find all keywords in A2:A10 and then list them in cell C11 with commas in between.So the result would be one cell C11 showing BLUE,GREEN,BLACK,ORANGE, etc.I have been trying to mess around with index and match and while I could get one to pull up I don't know how to combine it.

Keyword Range.xlsx‎

View 5 Replies View Related

2 Mathemical Functions Based On Text Value Of A Cell

Nov 10, 2009

I'm putting together a table which shows monthly costs versus Total costs. Is there a way I could use if two choose between two option dependent on the value of cell A1.

Example

A1 = "Totals", multiply Subtotal in A13 by 12.

A1 = anyother value multiply by 1.

Something like:-
=if(a1="totals",(a13*12), =if(a1<>""sum(a13*1)

View 2 Replies View Related

Calculate With Subtotal Functions And Write Value In Cell

Aug 22, 2014

i have wrote the following code snipped:

Sheets(2).Activate
Sheets(2).Range("D8").Value = Application.WorksheetFunction.Subtotal(109, Sheet(4).Range("BJ3:BJ" & b)) + Application.WorksheetFunction.Subtotal(109, Sheet(3).Range("BJ4:BJ" & a))

The debugger said: That "Sheet" is not allowed in the subtotal-function. because i would like to calculate two subtotals in two sheets an write the sum in another sheet

View 1 Replies View Related

Insert Different Functions Depending On Adjacent Cell Value?

Mar 20, 2014

I have six complex functions, which I would like to place in [A2] depending on what is placed in [A1], The functions themselves are quite extensive and I don't want to use any active functions (like INDIRECT) as this will cause slow calculations. [A1] will just be a given name to each of these functions.

View 3 Replies View Related

User Defined Functions And Cell Reference

Feb 28, 2012

referencing constant cells in custom functions. This is a sample of my code:

Code:
Select Case Name
Case Is = "Ball"
Valve_Days = [V4] + Cushion
Case Is = "Check"
Valve_Days = [V5] + Cushion

Now, the problem is when I open the workbook up, or someone downloads the file from the server, it resets all fields to div/0 based on what sheet the last user 'Saved' the document on. I think this is occurring because it doesn't know which sheet to pull the value V5 from? The problem never started to happen until I added another sheet in the workbook. Is there a way to tell VB that it isn't just cell V5, but it is cell V5 on Sheet 'X'?

View 2 Replies View Related

Combine Values For Cell Name

Sep 5, 2007

I have to combine two value and use them as a cell name,
So far this gives me the cell name =("A"&A3) where A3 has a value of 2 and the autcome for the code =("A"&A3) is A2 but I wanna use that as the cell name for my formula and not have it projected as a value only if you see what I mean.

Basicaly the reason for this is that when I write a formula in the cells and drug it down it moves 3 numbers at the time as the colomn H is split in 3 per row, so I have for example

=(B1)
=(B4)...

and so on, where I want it to be

=(B1)
=(B2)...

Therefore I am using the colom A to assign a row number, that the reason for the formula A"&A3

but when I try to write a formula it doesnt work as it doesnt use the outcome A2 as a cell name but as a text value only

View 13 Replies View Related

Combine Rows With Same Cell Value?

Nov 24, 2013

I have just two columns, one with numerical codes (A), the other ones with text (B).There are duplicate codes with different values, for which I would like to be able to combined their text values on a single row.

A | B
xx | text 1,
xy | text 2,
xz | text 3,
xz | text 4,
xu | text 5,
...

to be formated to:

A | B
xx | text 1,
xy | text 2,
xz | text 3, text 4
xu | text 5,
...

The resulting column B can be with/without space delimiter between the text values (there is a coma after each text value)

View 3 Replies View Related

Combine Different Cell Values In To One

Jan 6, 2009

I have a list of columns filled with data, say b thru d. What i want to do is combine the cells in the same row (b thru d again) and put that value in column a like this : valueb-valuec-valued

I want to keep going down rows until there is no data left and then I want it to stop. So far I have this (which doesn't work at all, as usual)

View 2 Replies View Related

How To Combine The Data In A Row Into One Cell

Nov 16, 2009

I am trying to combine the data in a row into one single cell.

For example,

B2= A1 & A2 & A3 .... until the end of the values in column A

I need this to be a macro so that when I enter values into column A manually and press the macro button, it will combine them into cell B2.

View 14 Replies View Related

Combine 2 Formulas In One Cell?

Feb 3, 2014

I would like to have cell A3 contain two different formulas. I may be doing this wrong, so here is what I would like to do:

Cell A1 will contain a number that will change from month to month, but will need to show as $0 until then. Cell A2 will contain a fixed sum number from month to month Cell A3 will contain the difference between cell A1 and A2

I wouldl like cell A3 show as $0 until a number is inputted into cell A1

A1
$0

A2
$2000

A3
$0

The formula I have in cell A3 is =SUM(A2-A1), but cell A3 shows $2000. I remember doing this before, cut can't remember the exact formula. Something like =if(A1

View 4 Replies View Related

Functions Compared With VBA Functions

Mar 14, 2008

I am aware of the following topic in the VBA Help file:

"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."

And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)

However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.

View 9 Replies View Related

How To Combine Date And Time In A Cell

Feb 25, 2013

i want to creat a sheet doing the following

enter the phone numer in cell a1
enter the phone break down hour in cell a2
enter the phone break down date in cell a3
combine the cell a2 & cell a3 in cell a 4
enter the phone fixed hour in cell a5
enter the cell fixed date in cell a6

combine the a5&a6 in cell a7 subtract a7-a4 and show the result in hours

View 2 Replies View Related

Macro To Combine Several Cells Into One Cell

Jun 8, 2014

Creating a macro that is a combination of other cell values. the cell order is listed below:

C57 = contains a unique number that will change from time to time eg. 1234
H57 = 1 (this will always be the value of 1)
N57 = contains text eg. AUDUSD
V57 = contains text eg. BS
AB57 = contains a number eg. 1.8954
AH57 = 0 (this will always be the value of 0)
AN57 = contains a number eg. 0.01
AS57 = contains a number eg. 1.7954
AZ57 = contains a number eg. 1.9954
BH57 = conatins either 1 or 0 eg. 1

the cell needs to contain the above examples like this :
BT57 =OpenPosition(1234,1,"AUDUSD","BS",1.8954/0,0,0.01,1.7954,1.9954,1)

This will be needed for a total of 14 rows (57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81, 83)

The cell with the results cannot have any cell references contained in the end results and must be formatted as above so that the add in program can read and pass on the information.

View 3 Replies View Related

Combine Several Lines Of Text Into One Cell..

Oct 23, 2008

In column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).

View 2 Replies View Related

Combine Column Into Single Cell?

May 10, 2012

I have a column of data that will vary in length and I'm trying to write a macro to combine it all into one cell. Basically I need to do the equivalent of entering "=A1&A2&A3..." for the entire column. I started by creating an array string variable and have the data in grp() but now I'm stuck trying to figure out some sort of loop to keep adding onto the same cell or something

View 6 Replies View Related

Combine Data From 2 Cells Into 1 Cell

Mar 13, 2013

Let's say there is a name (Big Bird) in cell A1

Let's also say that cell B1 has =Today()

How can I make cell C1 = Big Bird 3/13/2013?

View 9 Replies View Related

Combine Both (If) And (VLookup) Within Single Cell

May 23, 2013

I want to combine both an 'if' and a 'vlookup' within a single cell. So I want to say if the value in cell a1 can be found in columns c1:d100 then return a 'yes' if not return a 'no'. How do I do this

View 7 Replies View Related

Combine Userform Results Into 1 Cell

Jun 1, 2008

Is it possible to combine the results from 1 Text Box and 2 Combo Boxes to return to 1 Cell?

For example, i am using this code to write username to Sheet 2
Sheet2.[c9].Value = txtFullName.Value

and this code to write account number to Sheet 2
Sheet2.[d8].Value = txtAccountNumber.Value

BUT - i would really like to take the Value from txtFullName and take the Vaule from txtAccountNumber and combine that into (1 cell only) with space separators please.

Example : (username & account number combined)

Slim 12345678

View 9 Replies View Related

Combine Variables And Paste Them Into One Cell

Aug 6, 2008

I would like to combine variables and paste them into one cell. For example, I have a city stored in the variable "city", a state stored in the variable "state", and the zip stored in the variable "zip".

So I need something like...
Range("A1")=city, state zip

View 9 Replies View Related







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