I use Excel 2000 (9.0.2720)
In range A1:D10, I have entered the figures in actuals (e.g. 34532642.45, 78945624.89 etc.). How can I convert those figures in Lakhs / Thousands / Crores? Before converting, Can Excel 2000 prompt me for conversion in Lacs / Thousands / Crores?
I'm trying to see how accurate people's work predictions are to actual work completed. So I have these formulas:
=IF(C15=0,"",(SUMIF(L$24:IO$24,"Est.",L15:IO15))) - total estimated days
=IF(C15=0,"",(IFERROR((SUMIF(L$13:IO$13,"Act.",L15:IO15)),""))) - total actual days
But the problem of course is that people estimate a load of work and only fill in the actual days as they go along, so the accuracy of comparing one to another is almost always misleading.
What I want to do is only count the values in the weeks Estimated if the Actual figure is also there (L24:IO24), which is always the cell directly on its right.
I've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
View 1 Replies View RelatedI learnt that " " & can combine the text and figure. However, when it comes to a negative numbers, i dont know how to maneouver it. Eg, Cell A1 is USD(50,000.00) and the comment in Cell B1 is Cost is USD(50,000.00). So the function in B1 = "Cost is "& what to put here?
View 3 Replies View RelatedI need to find a number (1, 2 or 3) and I am looking in two different cells for it. Now, Cell 1 might have the information; Cell 2 might have the information or BOTH Cells A AND B might have the information, or either cell (or both) might have different information. I can't do an IF/OR because I don't know which cell will have it, nor can I do an IF/AND for the same reason. I can't assume that they will both have the information -- so I can't just use a solution that includes a "divided by."
for example:
A
1 2
2 0
or maybe
A
1 3
2 3
or
A
1 4
2 1
If A1 or A2 is > 0, but <4, then that number
how to get a date to return what the actual day? ie 13/01/09 is tuesday?
View 3 Replies View RelatedI constantly use the following to determine the last row in a worksheet for loop control purposes.
[Code] ......
In my current problem i have a worksheet that has been formatted such that the code above returns a value of ,say 150 where as the actual data stops at say row 34.
If I "watch" the value in Col A I see A34 = "1513(a)" which is valid data and A35 = ""
I can easily cycle through the worksheet and calculate the last used row myself but I am always looking for ways to let "VBA do the heavy lifting" when I can
I have a list of 5,000 urls and I'd like to see what the actual url that is returned for each of them. For example, if I run the url www.example.com/runningshoes, that url may redirect to www.example.com/shoes. I'd like to see what each url returns. Is there a formula/macro that I can use to see this? I'm not very experienced in using macros, but I'm willing to give it a shot if it'll work.
View 2 Replies View Relatedonce bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
I have a spreadsheet with a large amount of conditional formatting and formulas within it. In order to make the file smaller and more manageable for management, I need to copy the sheet into a new book and then paste special values it.
However, upon doing this, the conditional formatting remains active and so my formats all change.
How can I copy the formats, but not the conditions?
I got a list of data in many row. I need to erase some rows, With a macro, how do i select the actual row position.
I want to place a macro button on each row created. By pressing it, i want to activate a macro to erase the entire selected row.
All i need is to know how to select the active row of the selected cell activated.
I am looking to get a Live Clock changing every minute..
Lets say I put the Total Time in the Cell A2 = 15 minutes and I have a Command Button next to it "Start"..
On the Click Event I want the Actual Time time-stamped at that moment in the Cell A3 and in Cell A4 the Time should keep on Ticking...
Can someone attach a file in Windows 2003 i.e .xls format and also explain the code if possible.
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies View RelatedOn my worksheet, cell A1 and A2 both contain the value:
0.1655555
I've changed the number formatting to three decimal places so it appears on the sheet as 0.166.
In cell A3 I have a forumla =A1+A2 which currently comes back with the value 0.331 (0.1655555+0.1655555=0.331).
Instead of adding together 0.1655555 I want it to add the displayed value of 0.166 so it should then come back with the value 0.332.
I have tried searching for it but I must not be entering the correct criteria.
how to figure a person's age if all I have is that person's birthdate?
See xls file. I have this chart and the data labels are percentages instead of the actual value. How did these get that way? Under Chart Options there is a autotext box that is there but if I uncheck it, the data labels go back to the actual dollar value and that autotext box goes away.
I am totally baffeled by this despite serach the help, the internet and this forum. I am sure it is something easy I am missing.
Can some one provide some color on this?
I cut this from a larger book with many tabs because I am not able to send out the entire thing (proprietary).
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editing.
How to enable this.
Place the actual date in a cell. Here is my code
View 2 Replies View RelatedI discovered the following Macro (on another site). What I'm attempting to do is make data entry simple by entering a date such as 012209 and have a macro convert the entry to 01/22/09 as a legitimate date field. On entering 012209, the macro displays a value of 09/01/2021 with an actual cell value of 1/9/2021. I expected a value of 01/22/09.
Entering 010109 displays a value of 01/01/2009 with an actual cell value of 1/1/2009. I expected a value of 01/01/09 (I can live with this, just didn't expect a 4 digit year).
i am creating an add in that will allow me to display all pivot tables in a workbook, their name, sheet location and source data in al listbox (called LBPivot) on a form (called FrmPivot)
My problem is that i am not sure how to make sure it points the focus to the activeworkbook and not the actual add in workbook?
i have this file with macro. But it doesnt works how i want. I need all data exctly in forms as are. So it can not will be changed. I hope it will be works, if i define my macro other.
I find actual day in column C where are dates. But now it find only if is there value like now(). In this file it found cell D24, but i need, that it will find cell C24. I need search only actual day in column C.
I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.
I then try and write an if statement
e.g. =If(b1="Friday",1,0)
B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?
how would one change the actual code words - instead of using if statements i would like to have when statements that do exactly the same thing, i just want to write "when blah then blah"
View 9 Replies View RelatedI have this formula in E19: ...
View 9 Replies View RelatedI have created the following code by recording a macro. The ActiveCells (“A1.A5203”) were based upon the actual number of records at that time. I wish for this macro to be used in a template and the actual number of records will vary from time to time. What do I need to add to this code to allow the AutoFill function to operate and fill only the actual number of records that exist.
ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=8,""0""&RC[-3],RC[-3])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
I've attached a simplified worksheet which tracks the calls of a team of salespeople along with a pivot table showing their results for Jan-Mar. I've also included a summary of their planned calls for the period. Ideally what I'd like to do is have the pivot table show actual calls, planned calls and variance for each salesperson and each month as seen on A37-J42.
View 6 Replies View RelatedI am working on a spreadsheet for my farm. I have two sets of data I am working with. A very basic example is below:
What I am trying to do is find the actual median stud fee (i.e. if you had another column with $5000 listed 22 times, $6500 listed 34 times, etc. then you could find the median from there).
Seems like an easy thing, but I'm not sure if an array formula is what I need to use or something simpler. I don't have a lot of data right now (only a page) but will have much more soon.
i'm doing a process and in column N, i would need to have the following combinations:
A1
A2
C1
C2
H1
I1
so basically, a formula that would figure out the combinations available!
can this be done using a formula?
******** ******************** ************************************************************************>Microsoft Excel - Cost Calculator---V5---Try.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutK39=
KLMN39A12 40B 41C12 42D 43E 44F 45G 46H11 47I11 48J Cost Calculator
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a command button (code listed below) which is 1/2 working. I have a column of PID's (numbers) sorted in ascending order which includes multiple rows with the same PID. When the command is executed, I want to insert a new row after the last matched PID (this part of the code works fine),
or
Insert a new row for the non-existent PID but in the correct sorted location (this is what I can not seem to figure out how to get to work).
My data looks like this in the PID column.
PID (my header row in A1)
158
158
159
161
163
163
163
164
I can successfully insert another line 158, or 159, but can not determine where to insert a line for PID 160 (which is not in the list yet)..................
I have this great code, but I can;t figure out how to add more columns to it.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
If Target.Value = "Q" Then
With Target
.Value = ""
End With
Else
With Target
With Selection.Font
.Name = "Wingdings 2"
.Size = 22
End With
.Value = "Q"
End With
End If
Cancel = True
End If
End Sub
