Make Values Add To A Certain Sum By Assigning A Digit Or Chipher To It
Apr 23, 2009
I have a column of numerical values: fields a1 to a10. Then, I have four fields – c1 to c4 – in which I want sums of some chosen values from column a to be summed up. Eg., c1 is to be a2+a3+a9, c2 is to be a4, c3 is void, c4 is a1+a8. The other a fields are ignored, and no a field will be counted twice. Now, I want to put something –*a letter or a number or so –*into the fields b1 to b10, that makes the aforestanding value sum up to a certain field in column c. Like, I put a "1", or maybe an "a", into b2 and b3 and b9, and that makes a2, a3, and a9 sum up to c1. So I'd need something that says: if this cell is "1", add the value in the cell to your left to the sum that makes the value in c1. As you can tell by my writing, I'm no geek in these matters.
View 4 Replies
ADVERTISEMENT
Jan 4, 2013
Essentially, I'm arranging an excel spreadsheet to organise my ongoing sales and keep record. I would love to have a formula to calculate the appropriate comission for each sale.
The ranges are:
0-199 = 0 Comission
200-499 = 10 Comission
500-999 = 25 Comission
1000+ = 50 Comission
I managed to get the 0, 10 Comission to be processed correctly but sadly I've hit a brick wall as to how to include the 25, 50.
The formula I have so far is as follow: =IF(COUNT(D22),IF(D22
View 2 Replies
View Related
Jan 24, 2014
I have to assign 3 values to 3 letters, I have been using the LOOKUP function,
P=20, C=35, W=100
I have input the following:-
=LOOKUP(A1,{"P","C","W"},{20,35,100})
But for some reason it is inputting 35 for the value of P.
View 3 Replies
View Related
Apr 30, 2007
I am trying to assign a range of values with different types( date,time,integer) to an array. I am using the following command which works fine.
Dim vArr() As Variant
vArr=range("A1"). currentregion.value
However when I try to print the "time formatted" values in the second column of the range I can't. I can't use timevalue function as well cause it doesn't treat the values as string but integers. Why is this happening even when I declare the array as variant?
View 2 Replies
View Related
May 2, 2007
I am trying to assign a range of values with different types( date, time,integer) to an array. I am using the following command which works fine.
Dim vArr() As Variant
vArr=range("A1"). currentregion.value
However when I try to print the "time formatted" values in the second column of the range I can't. I can't use timevalue function as well cause it doesn't treat the values as string but integers. Why is this happening even when I declare the array as variant?
View 6 Replies
View Related
Mar 9, 2013
I'm making a spreadsheet for the homes I'm looking at purchasing and wanted a way to calculate automatically which one has the most things we're looking for.
So for example, if a home has a walk in closet, it would say "yes". If it doesn't it obviously would say "no".
Is there a way to assign a number value in a totals column where "yes"=1 and "no"=2?
Or a way to make colors equal a certain value?
Where I'd make all the "yes" items green and then a green cell = 1, a yellow cell = 2, and a red cell = 3.
View 5 Replies
View Related
Jul 9, 2006
Is it possible once you have assigned text a numerical value (example: Adam = 12) to add them together? (example: adam =12 and bob = 8, therefore adam + bob = 20)
View 9 Replies
View Related
Jul 7, 2009
How can I add an array of values to a listobject, preferably in one big chunk? How can I read a chunk of values from a listobject into an array?
For the latter, I've tried:
View 2 Replies
View Related
Jul 20, 2007
I have a formula which assigns a points score to letters in a range and adds them up. In the example below F=0, P=6, M=12 & D=18. =IF( COUNTIF(AT5:BE5,"="""),"",SUM(COUNTIF(AT5:BE5,"=F")*0,COUNTIF(AT5:BE5,"=P")*6,COUNTIF(AT5:BE5,"=M")*12,COUNTIF(AT5:BE5,"=D")*18)). Thus if F, P, M & D were in cells A1:D1 the result would be 0+6+12+18=36.
My aim is to be able to customise the values of F, P, M & D, using a table and a cell value. See the table below, where the first number in each row represents a cell value which the user enters into BH1, the second, third, fourth and fifth numbers represents the values assigned to the letters F, P, M & D.
10 0 1 2 3
30 0 3 6 9
60 0 6 12 18
90 0 9 18 27
120 0 12 24 36
Some examples of expected output: user enters 10 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+1+2+3=6. user enters 90 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+9+18+27=54.
View 4 Replies
View Related
Apr 3, 2008
I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?
View 4 Replies
View Related
Oct 29, 2011
I have 5 and 6 digit transaction references in one column. Is there a quick way of finding the max and min values of both the 5 & 6 digit sets ?
View 3 Replies
View Related
Jan 17, 2013
In A1
01234
In B1:k1
01 02 03 04 12 13 14 23 24 34
I am looking for a formula that will combine each digit together as a 2 digit value
Is this even posible?
The value in A1 could range from a 3 to 9 digit value.
View 3 Replies
View Related
Feb 20, 2007
I searched and found that to assign a name to a label based on a cell value requires the following
Label1.Caption = Worksheets("Sheet1").Range("A1").Value
which would assign the value in cell A1 as the caption for Label1. I've got a range of values in cells:
C4:N4
and I'd like to assign them as names to labels 1 through 12. How would I do that using VBA?
View 6 Replies
View Related
Jun 13, 2014
How to calculate a check digit in excel. The details to calculate this are as follows:
All variables in the calculation are positive integers.
We take each integer of the pro number and multiply it by a value and sum them to get a total.
An Example: 8 Digit Pro Number: 66988757
Pro Number: 6 6 9 8 8 7 5 7
Position in the Pro Number: 1 2 3 4 5 6 7 8
Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.
Using our example pro again:
6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8)
6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2
54+ 48+ 63+48+40+28+15+14 = 310
Take the Sum of the previous calculation and divide it by 11
310/11 = 28
(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).
Figure a remainder by multiplying the quotient by 11 and subtracting from the sum.
Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2
Check Digit = 11- Remainder
Check Digit = 11 - 2 ---> Check Digit = 9
Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.
View 3 Replies
View Related
Mar 2, 2009
I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...
View 4 Replies
View Related
Jul 3, 2014
I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").
I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).
The statement I had was:
=if(A1>=50,1900+A1,2000+A1)
I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.
It seems like it should be pretty simple, but I don't know how to word it in a macro.
Starting Column Example:
12
13
14
99
11
[Code] ......
For some reason I can't get rid of the borders...
View 6 Replies
View Related
Jan 31, 2013
I have the following line of code:
Code:
Range("D" & LastRow).Value = Area.Text + Level.Text
Area and Level are both comboboxes on a userform, I want them to both go to the same cell but with a space between them. Right now it looks like "AreaLevel" but I want it to look like "Area Level".
I assume that I can do this by making each value in the combobox have a space after it but I would like to know the proper way of doing it...
View 3 Replies
View Related
Oct 22, 2008
I've two collumns, after some index the values of cells are zero
how i can draw a chart without selecting those zero cells?
View 6 Replies
View Related
Nov 18, 2013
I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:
=IF('Final'!A5='Original'!A5, "-",'Final'!A5)
The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.
Is it possible for the IF function to search the formula in a cell, and not the value?
View 1 Replies
View Related
May 23, 2008
I have on my Sheet named "Data" in Cell K4
=CONCATENATE(L16,O25,L18,O25,K20,S25,N22)
The values of that cell become a formula.
I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.
The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.
Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?
View 9 Replies
View Related
Feb 26, 2014
See attached file with a smaller sample of the 1667 row table of data I am working with. What I am looking to do is make a list on another sheet of each every line with a county and have the corresponding Township and range with it. So if I have a cell and I select Audubon county, I would like a list to populate that has the 12 lines of Audubon county in column A, and the Township in column B and the Range in column C.
Excel Rate Example.xlsx
View 5 Replies
View Related
May 29, 2009
I do data entry for a webstore and one cell is used to map out the exact product category/subcategory path. I currently have to copy the appropriate path from a long list on one page and then paste it into the cell. I have to do this a hundred times a day. It would be nice if each cell within that column can have these values stored in them so I can just click on the cell and open up a drag down box and select the needed value. Is this possible to do in Excel?
View 2 Replies
View Related
Feb 26, 2013
How do I make a formula based on two incremental values
e.g I drag this down
=ROWS(A$1:$A1)*B$4
where B4 is say 0.2
I will get
0.2
0.4
0.6 etc
But what if I want to start from 5 so it will be like this
5.2
5.4
5.6 etc
View 2 Replies
View Related
Sep 26, 2013
I need to make an IF statement that when it's true it need to retrieve the values from a different column. With what function is it possible to just retrieves values?
I need to return the value from pivot table. Forgot to mention this.
View 4 Replies
View Related
Jul 17, 2013
I have a chart with percentages and most of them are >40% so I would like te values to start at 40 in the y axis but I would also want to have the 0% show (as it stands there is nothing there) It is a double y axis chart where the % is shown with just a dot and the other Y axis is the actual column. Im not sure if I'm making sense but I would just like to have the 1st value in the secondary y axis be 0 and the 2nd value be 40, and from that point forward scale in intervals of 5%.
So the values in the y axis are: 0%, 40%, 45%, 50%......
View 1 Replies
View Related
Oct 17, 2013
Is it possible to make tab names dependent on Cell Values?
I have 12 Tabs in Workbook 1
Tab 1 = "COVER PAGE"
I would like Tab 2 ='COVER PAGE'!A1
Tab 3 ='COVER PAGE'!B1
Tab 4 ='COVER PAGE'!C1
etc...
Is this possible so that I can add cells (between B1 and C1) at a later date and have all the tab names change depending on the values I put in the cells?
View 9 Replies
View Related
Nov 20, 2013
I have a file and i want to make an exact copy with only values and the layout.
I can do copy paste each sheet in the new file one by one. with first the values and then the layout.
But the file has lot of sheets so it would be great to find a faster way then do it one by one.
View 1 Replies
View Related
Apr 19, 2006
I have made spreadsheet that calculates my total cost of making ice cream mix based on ingredient costs of two variables (Van_Gal, and Choc_Gal), for example 550 gallons and 750 gallons respectively. The worksheet calculates the total cost of making the Vanilla Gallons and the Chocolate Gallons. I’m not a VBA or Macro wiz, and now I’d like to Make a macro that will allow me to make a “table” of calculated costs associated with different assumed Vani_Gal and Choc_Gal amounts and then print the values; and then go down a row and print the cost of another amount of Vani_Gal and Choc_Gals until some preset end has been reached. For example:
Van_Gal; Choc_Gal;TOTOutput;avg cost
550; 750; 1300; $4,000.00; $3.08
600; 700; 1300; $4,250.00; $3.27
Let’s say that I want to know what the tot cost and avg cost per gallon for various combinations of Vanilla and Chocolate Mix from 550 V and 750 Choc for various combos and have it increase in iterations of 10 Gallons (i.e. 560 Van and 740 Choc); Maybe I'd use a " loop" that repeats calculations and prints them for different combinations of Van/choc until 800 Gals Van and 500 gal Choc. Once I have a table of values, I could sort it and find an optimal production level (with the Minimum avg cost). how to make a macro that can crank out a table like this?
View 4 Replies
View Related
Aug 9, 2013
how can i extract all number values from a table column and list them on a separate worksheet? there are blanks in the table column, which have to be omitted.
View 14 Replies
View Related
Feb 17, 2014
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
[Code]....
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
View 2 Replies
View Related
