Enter a value and calculate in same cell
Is it possible to enter a value in a cell and have a calculation performed based on the entered value, within that same cell? ex. A1 = [Enter the value] * 5
anonymous
excel.newusers
15348 articles. 
2 followers.
4 Replies
430 Views
Hi this is only possible with VBA using an event procedure. Is this a possible solution for you? -- Regards Frank Kabel Frankfurt, Germany bb wrote: > Is it possible to enter a value in a cell and have a > calculation performed based on the entered value, within > that same cell? > > ex. > A1 = [Enter the value] * 5
frank.kabelDepends on how advanced. I have done research and coding in Access, so it won't be completely foreign. Thanks >-----Original Message----- >Hi >this is only possible with VBA using an event procedure. Is this a >possible solution for you? > >-- >Regards >Frank Kabel >Frankfurt, Germany > > >bb wrote: >> Is it possible to enter a value in a cell and have a >> calculation performed based on the entered value, within >> that same cell? >> >> ex. >> A1 = [Enter the value] * 5 > >. >
Hi
not that advanced :-)
As a starting point for further information have a look at
http://www.cpearson.com/excel/events.htm
For your example put the following code in your worksheet module (not
in a standard module). To get into the worksheet module right-click on
the tab name and choose 'Code'
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
If .Value <> "" Then
.value = .value * 5
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
bb wrote:
> Depends on how advanced. I have done research and coding
> in Access, so it won't be completely foreign.
>
> Thanks
>> -----Original Message-----
>> Hi
>> this is only possible with VBA using an event procedure. Is this a
>> possible solution for you?
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> bb wrote:
>>> Is it possible to enter a value in a cell and have a
>>> calculation performed based on the entered value, within
>>> that same cell?
>>>
>>> ex.
>>> A1 = [Enter the value] * 5
>>
>> .
Depending on what you want in the end..........you can put a small TEXT box inside a cell and put a formula in the TEXT box referencing another helper cell which would multiply your primary cell *5.........this would allow you to have both the basic number and the multiplied number in the same "cell"........you can format the TEXT box to make the borders white so they don't show.......... Vaya con Dios, Chuck, CABGx3 "bb" <anonymous@discussions.microsoft.com> wrote in message news:1b69801c420ad$6013adf0$a301280a@phx.gbl... > Is it possible to enter a value in a cell and have a > calculation performed based on the entered value, within > that same cell? > > ex. > A1 = [Enter the value] * 5 > > > >
croberts
Similar Artilces:
in formula a range cell reference eg) $A1:$A20 that is static when filling
if i want a cell reference to not increment when filling i put a dollar sign in front of it i noticed this does not work when you specify a range of cells eg ) $A1:$A20 although it is not an error when i fill in a formula it still increments the cell reference in each row but if i specify a single cell it does not. does anyone know the syntax for this, it's hard to word so i havent been able to find anything on google. thanks for your help! cheers, /sh You mean like this: =SUM($A$1:$A$20) ? -- HTH, RD --------------------------------------------------------------------------- P...
Combobox Value Will Not Change
Not certain how to describe this one. A combox is placed on a form. The form is called from a continuos list via a mouse down event. If Button = acRightButton Then DoCmd.OpenForm "frmTask", acNormal End If The form, frmTask, has absolutely no code behind it. It has a combobox with a row source type of "value list" and a row source of ""Complete";"Complete with a Problem";"Abandoned"". The form, frmTask, is set to allow edits, allow additions and allow deletions all = yes. The value in combobox cannot ...
xmlDocument element's value assigning with &= or += doesn't work?
ex in VB: oChild = XMLDoc.CreateElement("P") oChild3.InnerXml = "this is test data" '''''This doesn't work: oChild3.InnerXml &= " that i like to keep on hand" '''Neither does this: oChild3.InnerXml = oChild3.InnerXml & " that i like to keep on hand" So is it that you can't access the XmlElement's value like that? Never mind, i had an xml syntax error - thanks! "TS" <manofsteele@nospam.nospam> wrote in message news:%23UBLCw0RFHA.3496@TK2MSFTNGP12.phx.gbl... > ex in VB: > >...
Outlook 2003 "Enter Network Password" prompt
Hello. I using Outlook 2003 against our email server. Our email server is FirstClass 8. I am randomly receiving requests to enter my network password. I have entered my username and password and checked the save password checkbox but I just get prompted again. I do not know why I am being prompted for this information. It happens often enough to be disruptive at work. I have set Outlook to check for new mail every 3 minutes. Does anyone know what might be causing this? Thanks in advance. Ryan Taylor For Outlook 2000 or 2002 on Win 98, NT, or 2000: http://support.microsoft.com/?id=290684 ...
Copy Partial Cell Contents in Excel ? #2
I will definitely work through those responses and give them a try! Los this thread for a little while, but FOUND it again! Thank you very muc for your help Sirs! Best, Ma -- MacDubhga ----------------------------------------------------------------------- MacDubhgal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1424 View this thread: http://www.excelforum.com/showthread.php?threadid=25899 ...
Boolean values in schemas
Hi. I want to to use the xs:boolean type for one of my elements. But is it possible to make these boolean values be read as Yes/No instead of true/false? Should I create a custom type for that? Thank you. Julian According to the XMLSchema datatypes spec, the following are the allowed values for xs:boolean 3.2.2.1 Lexical representation An instance of a datatype that is defined as �boolean� can have the following legal literals {true, false, 1, 0}. If you need the values to be Yes/No it has to be a custom type derived from xs:string with enumerations "Yes" and "No" ...
How can I edit cell contents with a macro in Excel?
I'm trying to creat an Excel macro that edits cell data, remembering the edits, not just the resulting value or formula. If, for example, I want to delete all but the last three characters of a cell value, and I record a macro, editing the cell produces a line like: ActiveCell.FormulaR1C1 = "745". But I don't want the specific value. I want the process. I'm essentially asking for RIGHT(R1C1,3), but I can't use that formula in the macro because it would be a circular reference. What am I missing? Those same kind of functions are built into VBA: Option Exp...
move cells from vertical to horizontal
Is it possible to move text listed vertically in cells to be horizontal. Copy the cells Select a cell outside the range Go to Edit | Paste special Check the 'transpose' box, and click OK. -- Regards Juan Pablo Gonz�lez "Chris" <anonymous@discussions.microsoft.com> wrote in message news:08f901c4ad4d$26bec4e0$a501280a@phx.gbl... > Is it possible to move text listed vertically in cells to > be horizontal. Thank you sooooo much. >-----Original Message----- >Copy the cells > >Select a cell outside the range > >Go to Edit | Paste special &g...
Adding up cells that include text in them
I have a time sheet where I want to add the last 3 characters in each of the cells to get a grand total for the row. Here's a sample: WED THUR FRI SAT SUN TOTAL N3 - 7.5 N3 - 7.5 N3 - 7.5 N3 - 7.5 If I treat the 5 columns as A to E, the total in cell F2 might be the formula =value(right(A2,3))+value(right(B2,3))+value(right(C2,3))+value (right(D2,3))+value(right(E2,3)) The problem is that the cell B2 (THUR) is blank so Excel interprets this as an error. Can anyone offer me a formula that will work if it is blank? I also will have a total of 14 cells to add (2 weeks)...
Change the colour of a bunch of cells, based on one cell.
Hi, I have a montly on-call roster which is colour coded for each person on the roster. I have set it up that if you put a name in the Thursday afternoon session, it replicates the name through to the NEXT Thursday morning. HOWEVER, I would like to also modify the colour of those fields, so that if the person on-call swaps with another person, instead of using the Formatter Painter and changing each group of cells, it does it automatically. Something like IF (E14 = "Bob", (CELL.Colour="Red",CELL.Text="Bob"), IF (E14 = "Fred", ...
Select specific cell
I want to select the cell "G" in the ActiveCell Row? -- Thanks Shawn range("G"&activecell.row).select "Shawn" wrote: > I want to select the cell "G" in the ActiveCell Row? > -- > Thanks > Shawn ...
MSP 2007 Earned Value Over Time Chart
Hi, I've created an Earned Value Over Time Visual Report. The figures look ok but on the pivot table the values drop to zero after the status date. Just wanted to know if there is a of deleting the zeroed rows from the pivot table as it's quite annoying that on the graph the values drop to zero after the status date. I've tried to delete these rows and I get message saying that I cannot do this. I've tried to hide the offending rows as well but it still appears on the graph. Can anyone help? Hello Dean Nichols, Have you considered copying/past...
Keeping the exponent at a fix value in Excel
Hi When formatting in Scientific, how can you keep the exponent constant and allow the decimal point to move? For example If I have: 6.647E-03 1.741E-02 8.378E-02 How do I ensure that Excel keeps the E to E-3 and adjust the decimal point? Cheers WayneL On Sat, 07 Jan 2006 17:49:01 GMT, "WayneL" <home@wlawson-nomorespam.co.uk> wrote: >Hi > >When formatting in Scientific, how can you keep the exponent constant and >allow the decimal point to move? >For example > >If I have: >6.647E-03 >1.741E-02 >8.378E-02 > >How do I ensure that Ex...
Omit zeros in calculation
Is there a way/formula to omit records with a zero value when calculating an average on a report? In the Report Footer section, add a text box. Set its Control Source property to something like this: =Avg( IIf([Amount] = 0, Null, [Amount]) ) Substitute your own field name for Amount. This solution uses the fact that Access ignores nulls when calculating averages. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Whitney" <Whitney@discussions.micros...
Transaction cannot be entered
I am getting an error message "Transaction cannot be entered." in my cash account on Money 2003. It has worked fine and then all the sudden this message. Help In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >I am getting an error message "Transaction cannot be >entered." in my cash account on Money 2003. It has worked >fine and then all the sudden this message. Help Don't overwrite your backup file. See http://groups.google.com/groups?as_q=&num=50&as_scoring=d&hl=en&ie=UTF-8&btnG=Google+Search&as_epq...
Mortgage calculation after a large extra payment
Hello World, The information I am looking for is a math expression. If you would like to offer some additional explanations and comments and so on, that would be fine too, but remember, all I'm looking for is a math expression. I need a math expression, that can be used in Excel, to calculate the principal portion of a monthly payment, in a specific month, after a large one-time extra payment is applied the previous month. Let's look at an example. Question: What is the mathematical calculation (in Excel) that results in the number $418.15. Conditions: Loa...
HTML values
Howdy, I'm pretty new to extracting info from IE into excel so I need some major help here. I need to fill certain input values on a webpage, "click" the submit button, and from there I should be able to extract the info I need. Its the navigating the selection screen I'm having a problem with. The HTML values I need, I believe are contained in this section of the source code. So how do I change these input values in the html code to what I need and submit? <FORM name = F001 method=post> <input name="screen_id" type="hidden&qu...
converting a 4 figure number to time in active cell
Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm to lazy to keep entering the ":" all the time, i was wondering if someone knew how to change 1530 to 15:30 etc in the active cell for all cells G5 to I19 on all 5 sheets in my workbook!!! I think this has something to do with vba or macros and i've never used them before. If anyone can help me, I'd be very grateful. Thanks Pete Chip Pearson shows how you can do that here: 'Date And Time Entry' (http://www.cpearson.com/excel/DateTimeEntry.htm) Hope this helps. Pete Pee;29...
Average value only for the filtered area..
Dear, I have data in the range of cells A2:E1000. The row 1 has the headers to which i had given filters. The column E has a numeric value for which an average value is required at G1 for only those cells in column E which reflect by a filter in column C. For example, after selecting a particular name using the filter in column C, it resulted in 10 numerical values, the formula should calculate average value of only those 10 values and reflect in G1. Please help. Hi, Try this in G1 =SUBTOTAL(101,C2:C100) -- Mike When competing hypotheses are otherwise eq...
Absolute values
Is there anyway i can sum 20 columns horizontally for the absolute number in each column? One way: =SUMPRODUCT(ABS(A8:T8)) -- Best Regards Leo Heuser Followup to newsgroup only please. "BJC" <bcombis@bigpond.net.au> skrev i en meddelelse news:dGJdc.2524$ED.2064@news-server.bigpond.net.au... > Is there anyway i can sum 20 columns horizontally for the absolute number in > each column? > > Hi you may use the following array formula (entered with CTRL+SHIFT+ENTER): =SUM(ABS(A1:T1)) -- Regards Frank Kabel Frankfurt, Germany BJC wrote: > Is there anyway i...
How to hide a cell if a formula returns no value?
HI again all, what I'm after this time is a way to hide a cell by using a formula. This is pretty much solely for formatting reasons so that the sheet can be cut and paste into word from excel without empty cells coming up. I need something like this; cell a1 contains "whatever is entered into cell a1" in cell a2 =IF(a1="",Hide cell a2,a1) not an overly useful example I know, but hopefully you guys get the jist of what I mean, Cheers, =IF(A1="","",A1) VBA Noo -- VBA Noo ----------------------------------------------------------------------...
Excel Cell-formatting
Hi! Does anyone know how to set own format in cell for numbers have a number 4305605 and want 4.305 i.e numbers in tousand and a sign for tousand too or space like 4 305 I'v tried this #0(space);[Red] #0(space) and only get 4305 Plz Anyone, urgent TIA Tommy. If that "dot" is a decimal point and you want 3 decimal places shown in units of millions, then try this Custom Format... #,##0.000,, If, instead, that "dot" is your thousands separator and you want to display your number in units of thousands, then try this Custom Format... #,##0, Rick "...
How do I format an Excel cell to accept i) as an entry?
When I enter i) in an Excel 2000 cell it changes it to I). How do I format the cell to accept i) as the intended entry? Try deleting an entry from: tools|autocorrect options|autocorrect tab remove the i to I Yt wrote: > > When I enter i) in an Excel 2000 cell it changes it to I). How do I format > the cell to accept i) as the intended entry? -- Dave Peterson ec35720@msn.com ...
How to set the bar with same X-axis value in same colour?
I have a bar chart showing each team's values. Total have "A, B, C, D" 4 teams. 2 teams each day. Listed in X-Axis are date and team. eg. 1 Jan A, 1 Jan B, 2 Jan C, 2 Jan D. How to set the bar's colour to be the same to individual team? Please advise! FYI, i'm using MS Office 2007. I just noticed the answer given by Herbert Seidenberg at below in 7 Mar. Please ignore the question I raised here. A lot of thanks to Herbert Seidenberg! "Terry" wrote: > I have a bar chart showing each team's values. > Total have "A, B, C, D" 4 te...
Table row cell adjustment
I have a 3 column 30 row table in Publisher which will be divided into the equivalent of two tables horizontally, which will keep margins or other table settings the same. Is it possible in rows 1 and 16 to either eliminate the cells or reformat the rows by dragging the cells to the Left and Right margins? I would like to place a heading into these two rows which by using the present format would force carriage returns. Having 4 tables and struggling with margin alignment, there must be an easier method to what I am doing. In advance, Thanks, Frankd ...
if i want a cell reference to not increment when filling i put a dollar sign in front of it i noticed this does not work when you specify a range of cells eg ) $A1:$A20 although it is not an error when i fill in a formula it still increments the cell reference in each row but if i specify a single cell it does not. does anyone know the syntax for this, it's hard to word so i havent been able to find anything on google. thanks for your help! cheers, /sh You mean like this: =SUM($A$1:$A$20) ? -- HTH, RD --------------------------------------------------------------------------- P...
Combobox Value Will Not Change
Not certain how to describe this one. A combox is placed on a form. The form is called from a continuos list via a mouse down event. If Button = acRightButton Then DoCmd.OpenForm "frmTask", acNormal End If The form, frmTask, has absolutely no code behind it. It has a combobox with a row source type of "value list" and a row source of ""Complete";"Complete with a Problem";"Abandoned"". The form, frmTask, is set to allow edits, allow additions and allow deletions all = yes. The value in combobox cannot ...
xmlDocument element's value assigning with &= or += doesn't work?
ex in VB: oChild = XMLDoc.CreateElement("P") oChild3.InnerXml = "this is test data" '''''This doesn't work: oChild3.InnerXml &= " that i like to keep on hand" '''Neither does this: oChild3.InnerXml = oChild3.InnerXml & " that i like to keep on hand" So is it that you can't access the XmlElement's value like that? Never mind, i had an xml syntax error - thanks! "TS" <manofsteele@nospam.nospam> wrote in message news:%23UBLCw0RFHA.3496@TK2MSFTNGP12.phx.gbl... > ex in VB: > >...
Outlook 2003 "Enter Network Password" prompt
Hello. I using Outlook 2003 against our email server. Our email server is FirstClass 8. I am randomly receiving requests to enter my network password. I have entered my username and password and checked the save password checkbox but I just get prompted again. I do not know why I am being prompted for this information. It happens often enough to be disruptive at work. I have set Outlook to check for new mail every 3 minutes. Does anyone know what might be causing this? Thanks in advance. Ryan Taylor For Outlook 2000 or 2002 on Win 98, NT, or 2000: http://support.microsoft.com/?id=290684 ...
Copy Partial Cell Contents in Excel ? #2
I will definitely work through those responses and give them a try! Los this thread for a little while, but FOUND it again! Thank you very muc for your help Sirs! Best, Ma -- MacDubhga ----------------------------------------------------------------------- MacDubhgal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1424 View this thread: http://www.excelforum.com/showthread.php?threadid=25899 ...
Boolean values in schemas
Hi. I want to to use the xs:boolean type for one of my elements. But is it possible to make these boolean values be read as Yes/No instead of true/false? Should I create a custom type for that? Thank you. Julian According to the XMLSchema datatypes spec, the following are the allowed values for xs:boolean 3.2.2.1 Lexical representation An instance of a datatype that is defined as �boolean� can have the following legal literals {true, false, 1, 0}. If you need the values to be Yes/No it has to be a custom type derived from xs:string with enumerations "Yes" and "No" ...
How can I edit cell contents with a macro in Excel?
I'm trying to creat an Excel macro that edits cell data, remembering the edits, not just the resulting value or formula. If, for example, I want to delete all but the last three characters of a cell value, and I record a macro, editing the cell produces a line like: ActiveCell.FormulaR1C1 = "745". But I don't want the specific value. I want the process. I'm essentially asking for RIGHT(R1C1,3), but I can't use that formula in the macro because it would be a circular reference. What am I missing? Those same kind of functions are built into VBA: Option Exp...
move cells from vertical to horizontal
Is it possible to move text listed vertically in cells to be horizontal. Copy the cells Select a cell outside the range Go to Edit | Paste special Check the 'transpose' box, and click OK. -- Regards Juan Pablo Gonz�lez "Chris" <anonymous@discussions.microsoft.com> wrote in message news:08f901c4ad4d$26bec4e0$a501280a@phx.gbl... > Is it possible to move text listed vertically in cells to > be horizontal. Thank you sooooo much. >-----Original Message----- >Copy the cells > >Select a cell outside the range > >Go to Edit | Paste special &g...
Adding up cells that include text in them
I have a time sheet where I want to add the last 3 characters in each of the cells to get a grand total for the row. Here's a sample: WED THUR FRI SAT SUN TOTAL N3 - 7.5 N3 - 7.5 N3 - 7.5 N3 - 7.5 If I treat the 5 columns as A to E, the total in cell F2 might be the formula =value(right(A2,3))+value(right(B2,3))+value(right(C2,3))+value (right(D2,3))+value(right(E2,3)) The problem is that the cell B2 (THUR) is blank so Excel interprets this as an error. Can anyone offer me a formula that will work if it is blank? I also will have a total of 14 cells to add (2 weeks)...
Change the colour of a bunch of cells, based on one cell.
Hi, I have a montly on-call roster which is colour coded for each person on the roster. I have set it up that if you put a name in the Thursday afternoon session, it replicates the name through to the NEXT Thursday morning. HOWEVER, I would like to also modify the colour of those fields, so that if the person on-call swaps with another person, instead of using the Formatter Painter and changing each group of cells, it does it automatically. Something like IF (E14 = "Bob", (CELL.Colour="Red",CELL.Text="Bob"), IF (E14 = "Fred", ...
Select specific cell
I want to select the cell "G" in the ActiveCell Row? -- Thanks Shawn range("G"&activecell.row).select "Shawn" wrote: > I want to select the cell "G" in the ActiveCell Row? > -- > Thanks > Shawn ...
MSP 2007 Earned Value Over Time Chart
Hi, I've created an Earned Value Over Time Visual Report. The figures look ok but on the pivot table the values drop to zero after the status date. Just wanted to know if there is a of deleting the zeroed rows from the pivot table as it's quite annoying that on the graph the values drop to zero after the status date. I've tried to delete these rows and I get message saying that I cannot do this. I've tried to hide the offending rows as well but it still appears on the graph. Can anyone help? Hello Dean Nichols, Have you considered copying/past...
Keeping the exponent at a fix value in Excel
Hi When formatting in Scientific, how can you keep the exponent constant and allow the decimal point to move? For example If I have: 6.647E-03 1.741E-02 8.378E-02 How do I ensure that Excel keeps the E to E-3 and adjust the decimal point? Cheers WayneL On Sat, 07 Jan 2006 17:49:01 GMT, "WayneL" <home@wlawson-nomorespam.co.uk> wrote: >Hi > >When formatting in Scientific, how can you keep the exponent constant and >allow the decimal point to move? >For example > >If I have: >6.647E-03 >1.741E-02 >8.378E-02 > >How do I ensure that Ex...
Omit zeros in calculation
Is there a way/formula to omit records with a zero value when calculating an average on a report? In the Report Footer section, add a text box. Set its Control Source property to something like this: =Avg( IIf([Amount] = 0, Null, [Amount]) ) Substitute your own field name for Amount. This solution uses the fact that Access ignores nulls when calculating averages. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Whitney" <Whitney@discussions.micros...
Transaction cannot be entered
I am getting an error message "Transaction cannot be entered." in my cash account on Money 2003. It has worked fine and then all the sudden this message. Help In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >I am getting an error message "Transaction cannot be >entered." in my cash account on Money 2003. It has worked >fine and then all the sudden this message. Help Don't overwrite your backup file. See http://groups.google.com/groups?as_q=&num=50&as_scoring=d&hl=en&ie=UTF-8&btnG=Google+Search&as_epq...
Mortgage calculation after a large extra payment
Hello World, The information I am looking for is a math expression. If you would like to offer some additional explanations and comments and so on, that would be fine too, but remember, all I'm looking for is a math expression. I need a math expression, that can be used in Excel, to calculate the principal portion of a monthly payment, in a specific month, after a large one-time extra payment is applied the previous month. Let's look at an example. Question: What is the mathematical calculation (in Excel) that results in the number $418.15. Conditions: Loa...
HTML values
Howdy, I'm pretty new to extracting info from IE into excel so I need some major help here. I need to fill certain input values on a webpage, "click" the submit button, and from there I should be able to extract the info I need. Its the navigating the selection screen I'm having a problem with. The HTML values I need, I believe are contained in this section of the source code. So how do I change these input values in the html code to what I need and submit? <FORM name = F001 method=post> <input name="screen_id" type="hidden&qu...
converting a 4 figure number to time in active cell
Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm to lazy to keep entering the ":" all the time, i was wondering if someone knew how to change 1530 to 15:30 etc in the active cell for all cells G5 to I19 on all 5 sheets in my workbook!!! I think this has something to do with vba or macros and i've never used them before. If anyone can help me, I'd be very grateful. Thanks Pete Chip Pearson shows how you can do that here: 'Date And Time Entry' (http://www.cpearson.com/excel/DateTimeEntry.htm) Hope this helps. Pete Pee;29...
Average value only for the filtered area..
Dear, I have data in the range of cells A2:E1000. The row 1 has the headers to which i had given filters. The column E has a numeric value for which an average value is required at G1 for only those cells in column E which reflect by a filter in column C. For example, after selecting a particular name using the filter in column C, it resulted in 10 numerical values, the formula should calculate average value of only those 10 values and reflect in G1. Please help. Hi, Try this in G1 =SUBTOTAL(101,C2:C100) -- Mike When competing hypotheses are otherwise eq...
Absolute values
Is there anyway i can sum 20 columns horizontally for the absolute number in each column? One way: =SUMPRODUCT(ABS(A8:T8)) -- Best Regards Leo Heuser Followup to newsgroup only please. "BJC" <bcombis@bigpond.net.au> skrev i en meddelelse news:dGJdc.2524$ED.2064@news-server.bigpond.net.au... > Is there anyway i can sum 20 columns horizontally for the absolute number in > each column? > > Hi you may use the following array formula (entered with CTRL+SHIFT+ENTER): =SUM(ABS(A1:T1)) -- Regards Frank Kabel Frankfurt, Germany BJC wrote: > Is there anyway i...
How to hide a cell if a formula returns no value?
HI again all, what I'm after this time is a way to hide a cell by using a formula. This is pretty much solely for formatting reasons so that the sheet can be cut and paste into word from excel without empty cells coming up. I need something like this; cell a1 contains "whatever is entered into cell a1" in cell a2 =IF(a1="",Hide cell a2,a1) not an overly useful example I know, but hopefully you guys get the jist of what I mean, Cheers, =IF(A1="","",A1) VBA Noo -- VBA Noo ----------------------------------------------------------------------...
Excel Cell-formatting
Hi! Does anyone know how to set own format in cell for numbers have a number 4305605 and want 4.305 i.e numbers in tousand and a sign for tousand too or space like 4 305 I'v tried this #0(space);[Red] #0(space) and only get 4305 Plz Anyone, urgent TIA Tommy. If that "dot" is a decimal point and you want 3 decimal places shown in units of millions, then try this Custom Format... #,##0.000,, If, instead, that "dot" is your thousands separator and you want to display your number in units of thousands, then try this Custom Format... #,##0, Rick "...
How do I format an Excel cell to accept i) as an entry?
When I enter i) in an Excel 2000 cell it changes it to I). How do I format the cell to accept i) as the intended entry? Try deleting an entry from: tools|autocorrect options|autocorrect tab remove the i to I Yt wrote: > > When I enter i) in an Excel 2000 cell it changes it to I). How do I format > the cell to accept i) as the intended entry? -- Dave Peterson ec35720@msn.com ...
How to set the bar with same X-axis value in same colour?
I have a bar chart showing each team's values. Total have "A, B, C, D" 4 teams. 2 teams each day. Listed in X-Axis are date and team. eg. 1 Jan A, 1 Jan B, 2 Jan C, 2 Jan D. How to set the bar's colour to be the same to individual team? Please advise! FYI, i'm using MS Office 2007. I just noticed the answer given by Herbert Seidenberg at below in 7 Mar. Please ignore the question I raised here. A lot of thanks to Herbert Seidenberg! "Terry" wrote: > I have a bar chart showing each team's values. > Total have "A, B, C, D" 4 te...
Table row cell adjustment
I have a 3 column 30 row table in Publisher which will be divided into the equivalent of two tables horizontally, which will keep margins or other table settings the same. Is it possible in rows 1 and 16 to either eliminate the cells or reformat the rows by dragging the cells to the Left and Right margins? I would like to place a heading into these two rows which by using the present format would force carriage returns. Having 4 tables and struggling with margin alignment, there must be an easier method to what I am doing. In advance, Thanks, Frankd ...