Equally Divide Value From One Cell Into Array
Jan 11, 2012
I am trying to do:
Say value in A2 and A3 is 4.5 and 5 respectively
and the value in A1 is and 1 respectively
B2 and B3 have zeros.
Case 1 :
Now I would like to have value A1 in C2:AZ2 till the value in A2 gets exhausted.
Hence C2 = 1, D2 = 1, E2 = 1, F2 = 1, G2 = 0.5, H2 = 0, I2 = 0 ... and so on
Case 2 :
Values in C3:AZ4 should be A1 till A3 gets exhausted. But the constraint is the values in the above range should start from/next cell when we left CASE1
Hence C3 = 0, D3 = 0, E3 = 0, F3 = 0, G3 = 0.5, H3 = 1, I3 = 1, J3 = 1, K3 = 1, L3 = 0.5 ...
I am using the below formula -
For CASE-1 : C2 is
=IF($A$2>0,IF(SUM($B$2:B2)$A$2,$A$1,0),0)
D2 is
=IF($A$2>0,IF(SUM($B$2:C2)$A$2,$A$1,0),0)
and so on...
But this works only if the numbers are whole numbers, I tried a lot but I could not get a formula which works with integers. The above formula will fail if the number is say 4.5 -having decimal part.
For CASE-2 : C3 is =IF($A$3>0,IF(COUNTIF(C$2,1)>0,0,IF(SUM($B$3:B3)$A$3,$A$1,0)),0)
D3 is
=IF($A$3>0,IF(COUNTIF(D$2,1)>0,0,IF(SUM($B$3:C3)$A$3,$A$1,0)),0)
When the numbers in A2 and A3 are decimals and not the whole numbers.
View 9 Replies
ADVERTISEMENT
Apr 4, 2009
I have an array in my code and I need to divide each elements of the array with a constant.
View 9 Replies
View Related
Jan 25, 2012
I need VBA code for picking up six random numbers from 1 - 30
5 times and put them in the columns from A1 :E 6
in A1 :A6 six random numbers
in b1:b6 another 6 random numbers ;
Without any duplication in each row or in each column .
View 5 Replies
View Related
Apr 15, 2006
The data is a column of zipcodes, a column of timezones for each zipcode, along with a column of this past years sales leads, showing a count of the number of leads from each zipcode. I want to assign territories to 10 sales agents based on an equal division of the number of leads in each time zone; that is, based on last years leads each agent would be assigned zipcodes in each time zone so that all agents would end up with the same number of leads in each time zone.
View 3 Replies
View Related
Jul 15, 2014
We have 5 - six month programs and five - 12 month programs we are going to sell; each have a different price. I Want to insert number of projected sales manually for each month and then have excel distribute/spread revenue evenly over six or twelve months into a separate set of cells. First payment would be received at time of sale.
View 4 Replies
View Related
Dec 16, 2009
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).
These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
View 9 Replies
View Related
Mar 10, 2014
I have a lot of cells containing numbers. Some numbers are small, some are big.
I want to divide all numbers which are greater than 300 by 1000.
IF cell A2 > 300 THEN divide by 1000.
How to do that? Is it possible to convert value in the same cell like using conditional formatting principle?
View 7 Replies
View Related
Mar 24, 2007
How do I enter a value into a cell and divide that number automatically by another value all within the same cell.
For example..
I want to enter 20 into a cell and have it change into 17.24 (20/1.16) automatically all within that same cell.
View 3 Replies
View Related
Apr 9, 2009
I am trying to write a macro where I divide the value of one cell by that of a second cell, multiply it by 500 and then to paste the outcome of the formula to a third cell. This is what I wrote
View 5 Replies
View Related
Apr 11, 2014
I have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.
test sum skip blank cells.xlsx
View 2 Replies
View Related
May 31, 2013
Column (B) contains two different units, either (ug L-1) or (mg L-1). I have this formula, =IF(B2="(ug L-1)",A2/1000,A2), which copies the value in column A to column C, so for cells that are in (mg L-1) units, it's a direct copy, but for cells that are in (ug L-1) units, it divides the value in A by 1000. However, I can't remember what the formula should be if the cell contains a "<" symbol AND is also in (ug L-1) units.
e.g.
< 0.004(ug L-1)
How can I change that to <0.000004?
View 7 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Nov 15, 2007
=CORREL(C1:C10,C12:C21) at H1
=CORREL(C1:C10,C23:C32) at H2
=CORREL(C1:C10,C34:C43) at H3
etc
can i have a macro that first array remain the same, and second array always 11 cells added. drag it down also can
View 9 Replies
View Related
Jul 9, 2008
I am wondering if it is possible to create a formula in a cell that will divide whatever number is entered into that cell by a set or fixed number.
ie. I enter the number 9 in the cell and when I press enter, the 9 is instantly divided by 15 and the answer is displayed in the cell where I just typed the 9.
View 14 Replies
View Related
Feb 27, 2009
Im wondering if this can be done in excel..
2 columns
if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%
this is the way I was trying to do this...
=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)
I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
View 9 Replies
View Related
Apr 8, 2014
I have conditional formatting on a cell based on a formula, when I entered the solution of "if then 0 " but the cell doesn't see the result as a 0, if I manually enter the 0 the conditional format works.
I even tried putting the formula in another cell and then referencing to that cell, still didn't work...
The "divide by zero error won't trigger the conditional either......
[Code] ....
View 3 Replies
View Related
Oct 10, 2008
If I have a value of lets say 53, I need it broken down into equal and whole numbers across the spread sheet with the last one being the remaining value.
Currently for example in cell F5 I have a value of 41 that I need to break down so
I have done the following:
In Cell P5 =IF(F5>=25,F5-21,F5)
Then in Cell Z5 =IF(P5>=25,P5-21,P5) and this goes on until I have zero value from the original value in the last cell can i not do this all in one formula and have the results populate in other cells.
View 3 Replies
View Related
Aug 20, 2009
I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.
She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.
How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?
View 12 Replies
View Related
Feb 2, 2014
I have just started to try and learn VBA for excel. the resource I have been using are books and the WEB. Some how, not sure how there was/is some code where all enteries in all worksheets are divided by 100, i.e if I enter 2222 after clicking the enter button the result in the cell is 22.22. I have deleted all macros and code however the problem still exist.
View 3 Replies
View Related
May 21, 2009
If CheckBox1.Value = True Then
Range("e25") = ComboBox6.Value
ElseIf CheckBox2.Value = True Then
Range("e25") = ComboBox6.Value / 2
End If
Im getting an error on the second to last row, Ive never tried to divide using VBA before and I have no idea how to!
Also the amount im trying to divide will always be in the format hh:mm
View 9 Replies
View Related
Oct 16, 2006
I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?
View 6 Replies
View Related
Apr 7, 2014
I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.
format of sheet
Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status
I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.
How do I do it without manual labor?
View 1 Replies
View Related
Aug 1, 2014
I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 3 Replies
View Related
Aug 1, 2014
I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 10 Replies
View Related
Jun 15, 2009
Excel has a QUARTILE function to divide a population into quartiles. I'm trying to do something similar but divide a population into deciles (i.e., every 10%). Is there any easy way to divide a large population into deciles and to assign a decile to each value in the population (i.e., top 10%, 11-20%, 21-30%, . . . bottom 10%)?
View 2 Replies
View Related
Jun 20, 2014
I have a data extract that gets generated at the end of each month that I post into the 'Full Extract' tab on my example spreadsheet.
writing a macro that will divide the various items in the 'Extract' based on the Reference, (some items may need to go into multiple sheets)
then draw down the formulas that need to be applied to the item.
View 2 Replies
View Related
Sep 6, 2005
=1/25%
is it that you want a UDF
Function MyFun(a,b)
MyFun = a / b
End Function
Usage:
=MyFun(1, 25%)
Mangesh
"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message
news:B257DADD-0042-4CF8-88DF-1D17AEAFF627@microsoft.com...
View 14 Replies
View Related
May 11, 2009
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:
4+4/636 = 79.5
d2+f2 (divided by) h2 = 79.5
View 3 Replies
View Related
Aug 27, 2009
I really struggle with the following scenario:
A1=5 B1=4 C1=0 D1=0 E1=5
What I want to do is to divide the sum of those cells with the number of cells with a value > 0 (but all cells must be controlled to check if the value > 0).
In real life it's easy to see that I want to do 14/3 (A1>0, B1>0 and E1>0 equals three cells), but how to write this formula in Excel?
View 3 Replies
View Related
Jan 6, 2012
How to divide two cells if there is no text in the first?
i.e.: if there is text display a 0 and if there is a number to divide it with a second number in another cell.
View 7 Replies
View Related