I am trying to generate random numbers between upper and lower limits (eg 120,140). The randbetween function in the help file is ideal....but when I use it it generates a #Name error.
(I am using Excel 2002 and have the analysis toolpak add in ticked).
I have a file that has been produced using Statistica software. The file is supposed to have around one million rows, and when exported to Excel the file is about 30 MB. When I open the .xlsx file in Excel 2010 only two pages of data appear. The rows also have weird numbering. The first square of the A column is A1. The second is A3833, the third is A6789, then A8161, then A8162, then A8163, then A8164, then A18070.
The scroll button to the left of the screen is long, as if the document was only two pages long. When the scroll button is click-and-dragged a small beige square appears with a row number. This small square seems to be aware that not all rows are shown as it shows many more rows than are visible to me.
I am looking for a way to show the character location number of a text file, possibly in the first row or a macro that I can run at any given location that will give me the location # I am currently viewing.
Currently, when I open the file I can see the character # at the opening screen (see attached file) but they disappear when it actually converts. I would like to be able to keep the character location ruler once the file is opened in Excel so I don't have to manually count.
I have tried
random_result = WorksheetFunction.randbetween(0, 36)
But failed.
Whats the VBA equivalent?
I'm using the RANDBETWEEN(1,15) excel function to fill a range of cells with random numbers between 1 and 15, the problem is that each cell cannot be the same number than any other in that range, how exactly can i accomplish this?
I attached a file for a better example, if you hit F9 you can see that sometimes at least 2 cells share the same number...but I need it to be different on each one, and still be random.
how to use the =randbetween formula
I need random dates from 25 years ago to today
I am trying to use the formula below in a macro to produce the same result, but I am having trouble with the coding.
View 4 Replies View RelatedI need the solution for the following:
1. Currently I have a randbetween() formula in cell B2.
2. Cell A1 = B2 (Every time the value in B2 changes so does the value in A1)
Instead, I want that every change in value in cell B2 is captured in progressive cells.
For example:
First iteration in cell B2, value is entered in A1.
Second change in value in B2, value is entered in A2, while value in A1 remains unchanged and so on and so forth.
Is it possbile with the randbetween function to only randomate a number when a certin object is pressed? As at the moment, every button i press or drop down data i select it will randomate a number, which i dont want to happen.
View 2 Replies View RelatedI have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
View 4 Replies View Relatedwhy my zero totals sometimes show as 0.00 and other times as (0.00), when they have the exact formula?
View 2 Replies View Relatedgot this quite complciated formula.
any of the cells specified can be blank at start of spreadsheet
is there a way to hide the DIV/0 error from appearing
this is the formula:
=((((((SUM(C11:C14)*(100%-R2)+(SUM(ThroughputFormulas!C33:C36)*R2)*(L2/30))/1000)*N2)*3600)*P2))/(SUM(C39:C45))/1024/8
PRODUCT shows a value even if 0*# =IF(H15*I15=0,"",PRODUCT(H15,I15))
View 5 Replies View RelatedI already have a conditional format which is working OK. I would like to put a formula in the cell so it shows a % so currently it would be 100% as there is 3 cells below showing 3 greens.
Alternativily each colour is worth a value - Green = 1, Amber = 2, Red = 3. So Cell I3 would show 3.
I have a problem with my formula. As you can see, i want to get the CIM number of the Team Leaders (TL Name) when i change the dropdown list of the campaign. But what im getting is the repeated CIM numbers.
Here's my formula:
=IF(ISERROR(INDEX('Team Data'!$F$2:$F$5489,SMALL(IF('scorecard (OM Search)'!$C$12='Team Data'!$E$2:$E$5489,ROW('Team Data'!$E$2:$E$5489)-MIN(ROW('Team Data'!$E$2:$E$5489))+1,""),ROW(A1)))),"",INDEX('Team Data'!$F$2:$F$5489,SMALL(IF('scorecard (OM Search)'!$C$12='Team Data'!$E$2:$E$5489,ROW('Team Data'!$E$2:$E$5489)-MIN(ROW('Team Data'!$E$2:$E$5489))+1,""),ROW(A1))))
I want a macro to show a message with a content in the cell B3.
For EG: "Rec as of 'B3' is created"
This has to be the message and 'B3' has to be the content in the cell B3.
I tried doing it but i am unable to show the content in B3 in the message.
I'm using Excel 2007 and have created a spreadsheet for invoicing. I have created a dropdown list which then imports the address data using vlookup.
My problem is this. If there is no text in say, address field 4 the resulting field in the invoice shows as a 0 eg,
NAME
ADD1
ADD2
ADD3
0
POSTCODE
Is there a way to move the Postcode to the empty cell showing 0? and/or how do I make the cell to show blank instead of a 0.
I am trying to create a basic invoice spreadsheet.
On worksheet 1, I have days of the month A4 to A35. Columns B, C, D, E are headed.
Every hour of work I would enter a " 1 " in the correct cell for the day. For example, 1 hour, every segment of the day, I would enter "1" in cells Bx, Cx, Dx and Ex. This continues for the month;
On a 2nd worksheet, this is neatened up, and I have added formulas to work out the cost and type of call;
On the third worksheet, I want to display roughly the same, however when there has been no work on the day (ie the date displays "None" and the adjacent cells show: blank, blank, blank, blank, £0.00), I want it to skip the cell and display the next date which has data to show (ie a date). The data on the adjacent cells needs match with the date (in the same way as physically deleting a 'non worked' whole row).
This then on the invoice worksheet would look neat and display only the days actually worked.
What formula(s) could I use to do this.
I have a WYSIWYG problem: what I see on the screen is NOT what I get out of the print job.
I commonly have the problem when working with Excel objects (any kind of shape, text box, line, graph) in that the layout looks great on the tab that I created, but when I go into PrintPreview or actually print it out, the shapes don't quite align with what I had built in the tab. Where I thought I had lines joining, the print out shows them crossing at other points. If I then go back to the tab and look at the layout on the regular screen, it looks fine.
This problem can be easily understood if you try to create a triangle by drawing three lines (non-overlapping lines that cross only at the vertices). Note that the lines either prematurely cross, or don't intersect at all when you print it out.
THis problem is even magnified when using the camera function to take a "snapshot" of a graph or other objects. In fact, it has the tendency to even "mess up" the graph's formatting, regarding the arrangement of titles and axes (often making them overlap).
Hi i have the following SUMPRODUCT formula which works as required, but when i clear the raw data and apply new raw data and apply macro that sorts my data, then all the ranges in the formula shows #REF!
=SUMPRODUCT((Raw_Data!C6:C5000=Summary!$B10)*(Raw_Data!A6:A5000=Summary!C$8)*(Raw_Data!E6:E5000>2))
Excel 2007
I am trying to clean up a template that shows #DIV/0! in the cells that contain the formula below, when there is no value present in the NamedReference.
=SUM((B6/1024)/NamedReference)
I need some vba code to
a) determine which of excel's menubars is active (Worksheet Menu Bar or Chart menu Bar)
b) delete the controls ie File, Edit, View etc menus from that menu bar
c) restore the controls to that menubar on workbook close
Normally this wouldn't be required but my version of excel is 2002 which has the Worksheet Menu Bar active and the work version is excel 2000 which has the Chart Menu Bar active.
I don't know why the Chart Menu Bar is active on the work system as there are no charts in my project.
Most of the development takes place at home so i need to be able to use vba to perform the above tasks
The menubar itself must remain active so i can add a custom menu to it.
I have 2 forms (A and B). Form B is initialized with values from a global array created in form A.
When closing the B form it's still visible.
I want it to dissapear.
The B form is opened from the A form
Private Sub cmd_ok_Click()
frm_B.Show
End Sub
When closing the B form it's still visible (modal)
'Within form B
Private Sub cmd_cancel_Click()
Unload Me
End Sub
I'm having an issue with a worksheet that I created. Apparently, every time that I go to open it, it gives me a Grey blank screen. Yet, I know the worksheet is behind this Grey screen. I went to full screen and I can see the work sheet in full view, but it always opens in this Grey screen. I'm not sure why its doing this, no other worksheets open in this matter. I have tried reinstalling excel and it still didn't fix this problem. But it is only happening with one particular worksheet. I'm sure it is just a setting that I might have changed, but I can't figure it out. The file isn't corrupt because the macros still work, I tested them.
View 7 Replies View RelatedHow come the WEEKNUM formula in the attached file does not work?
The cell gives me "#Name?", and I have no idea why.
I noticed one thing though, which is that WEEKNUM function is described when I look in "Help", but the function is not in my "Insert Function"-wizard, so perhaps the function is not there at all, which is very odd.
I have an Excel workbook that runs some code in the back. When I close the workbook file it seems to close OK. However if I then open the VBA editor window I still see the file in the project explorer. If I re-open and then close I have 2 instances in the project explorer... and so on. I have tried setting all objects to nothing etc. but the problem still manifests itself.
View 4 Replies View RelatedI am facing an issue related to IF formula. It does show the correct result in one and another cell, but in a few others - doesn't.
This is strange since all variables are correct, and parameters are the same for every cell. And, in theory every cell should show the right answer.
In addition, the result doesn't change even when changing variables in cells that are dependants to cell where IF is located.
Concerning the formula itself. Variables are as such:
- different container types (20, 40, 40rf, 45cs, 45rf, 45)
- different weight limitations per container type (e.g. 20 container max weight 26t)
- using IF(OR(AND(....
- if any of the conditions met (e.g. 20 container < 26t ) then VLOOKUP for the rate from data based on the place name.
- if non of conditions met, then show "Overweight cargo"
I have following formula:
Why does this formula show result as "FALSE"?
If cell B56 is empty, then I need a result of "". i.e. blank result, but it shows FALSE word.
IF(LEFT(B56,2)="on",
then you go straight to the TRUE nested IFs
IF(LEFT(B56,2)="on",
IF(LEFT(B$52,3)="Pla",RIGHT(Table!AN4,2)&"% Discount",
IF(LEFT(B$52,3)="Gol",RIGHT(Table!AO4,2)&"% Discount",
IF(LEFT(B$52,3)="Sil",RIGHT(Table!AP4,2)&"% Discount",""))), "need something here" )
so to get ""
IF(LEFT(B56,2)="on",IF(LEFT(B$52,3)="Pla",RIGHT(Table!AN4,2)&"% Discount",IF(LEFT(B$52,3)="Gol",
RIGHT(Table!AO4,2)&"% Discount",IF(LEFT(B$52,3)="Sil",RIGHT(Table!AP4,2)&"% Discount",""))), "" )
I'd like to add shades to a shape which shows a percentage of remaining solution as the percentage fluctuate the shadow percent does also.
View 10 Replies View RelatedI need a lookup table. I have a table that shows what the sun is doing for each day of the year. Now I need to input longitude and time zone, add or subtract if the sun is fast or slow on that day
I need it to work off one formula not make 365 copies