I am trying to write a macro where the cell value will be decreased by 1 each time the macro is run, down to a minimum value of 0
The code I have to do the reduction is below, and Im guessing its a simple one liner to check the value before applying the reduction:
Sub DecreaseValues()
'
' Decrease values by one Macro
'
'
Range("B29").Select
[Code] ........
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies View RelatedI want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
Daily At the end of day We receive sales files from our different teams, these files are received at different times due to difference in working shifts. I have collated last one month data and from this data I want to know the earliest and maximum time and date on which we received the files in last month, so that i can fix the receiving time for all regions.
View 5 Replies View Related I have data from Columns A to D. I want to do the
following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
For example:
A B C D
Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....
If the result in cell i1 less than 1 to change it to 1
View 2 Replies View RelatedI am working with a spreadsheet that has quite a few products and each product has a column for each vendor of that product with the price associated with that vendor. So, I have five columns total:
product, vendor 1 price, vendor 2 price, vendor 3 price, vendor 4 price. I want to have a 6th column that will show the preffered vendor, which of course is selected by the lowest price. I have been able to accomplish this by chaining IF statements together, but it is getting VERY cumbersome for more than 2 vendors.
Ideally, I would like to tell Excel to look at the 4 price cells, choose the lowest one, and return a column number, which I can then associate with a vendor.
I have a row that is a percent of sales with this:
=(F4*0.2+G4*0.1)*I4
"I4 will either be 1.0 or 0.5"
Now I want to have it so if this equation equals less than 100 with I4 being 1.0 to equal 100 OR if I4 is .5 then it should be 50. I have looked all around and I am no excel wizard so this is troubling me significantly.
I would like to generate an average of Row 1 to Current Row in a column. The Average column is the column that I would like to know how to generate.
Date
Value
Average
08/04/2012
5
5
08/04/2012
10
7.5
[code]....
I got close with =AVERAGE(OFFSET($D$1,COUNT($D$1:$D2),0,COUNT($D$1:$D2))), but after a couple rows of it being correct, the average starts to fall.
Using the above:
Value
Average
Should be
7.07
7.07
7.07
7.23
7.120
7.15
[code]....
I'm trying to analyze the pricing we get from our shipping courier. Although the courier gives us incentive discounts, they also have a minimum charge that supersedes the discount. In other words, suppose the "Minimum charge" is set at $6.24. If the published "list rate" on a package is $10 but we get a 50% discount on all shipments, instead of paying $5, we pay $6.24. Basically, between the post-discount amount and the "minimum charge" we pay whichever is greater/higher.
Now, to the Excel part: I have cells with simple formulas applying our % discount to our carrier's published list rates to calculate the resulting price (i.e, where cell A1 contains published list shipping rate and cell B1 is our discount %, so in cell C1, I have a formula: =A1*B1). I would like know how, if the result of the formula in cell C1 is less than $6.24, that the cell could display $6.24 rather than the actual result?
I have 4 cells and I want to highlight the lowest valued cell.
At the moment I have the conditional formatting formula of
=AND($C$5=MIN($C$5:$C$10),$C$5>0)
However if C5 = 2 and C9 = 0 then C5 doesnt get highlighted as 0 is lower than 2.
However I do want it to be highlighted. How can I ignore the zeros in the range im testing?
I have a column that indicates the earliest date that a project will start. There is a Project Minimum Date, Activity Dates, Sub Activity Dates and Sub Sub Activity Dates.
When a Sub Sub Activity Date is entered, it goes up to the Sub Activity Date and so on.
I have a formula =SMALL($E6:$E224,COUNTIF($E$6:$E$224,0)+1) that will select the earliest date out of the sub sub activities and sub activities but if one of the cells is blank in the sub activity or activity, then a #NUM! error is returned.
If I have a column full of data (call it 5000 data points) and use =min(a1:a5000), how do I find the cell this function is referencing without sifting through 5000 entries?
View 6 Replies View Relatedi have the sheet which have the table which look like
Person task done
A 5
B 4
C 1
D 1
now i want is there is any way to find minum value in task done colium and return the person name and like in this exapmle, 2 person have done have done minium work.. it tell me both those name
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)
If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.
This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View 4 Replies View RelatedI'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.
Here is the ....
i'm trying to reference a cell value in the column to the left of a "min" value:
-A-------- B
1200------20
2400------15
2300------18
900-------22
i'm using the "min" function to select min value from column B (15) but only as a means to use the A column value (2400) in another formulahelpful
How can I find the lowest value in a range (say B5:H20),
and then sum its column ???
i.e. The minimum value in this range is in the cell F16... So, I need to sum range F5:F20 (only the column with the minimum value)
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.
=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)
=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)
Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
I have a table populated with equations. I need to write a function to find
the smallest value in that table. However, I want to ignore the zero values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.
I am looking to create a formula to determine the minimum value within the top 80% of a range, but relative to certain values in other columns.
e.g.
Column A Country
Column B New Customer
Column C Revenue
I need to determine what the minimum value is for the top 80% of revenue in column C, but only including Country=UK in column A and New Customer=Yes in column B. This will tell me that the top 80% generate at least £x in revenue
how can i do the following equation?
40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ tO 3000$
100$ if the sum is greater than 3001$
C16 X C19 if sum is greater than 12000$
all should go in one cell
I am calculating several cells which in certian circumstances I want the amount to be limited to a max amount. I have a cell that will be used to indicate whether or not to limited the amount, but I am not able to come up with a viable formula. Here is my SUM Formula info:
My current calculation
=SUM(S16:S18,S21,E22:R22)
The cell that will drive the restricted amount
I7(H or S). If S is used in this cell the amount should be limited to 80. If H is used then the calculation should be exact.
I have a spread sheet that needs to compare numbers in a row & hightlight the lowest number in each row.
View 9 Replies View Relateddata in 3 colums (A2:c100)
Would like to sum the maximun values in each row together.
is Max(A2:C2) + Max(A3:C3) + Max(A4:C4) ........
I assume whatever works with max I can change to min.
I searched and found a couple things dealing with zeroes or other errors, but not the #N/A errors.
I have a range of numbers in A1:a100. To make this range linegraph-friendly, I have all blank or zero entries defaulted to appear as #N/A. Now the problem is that I would like to find the minimum number in the range, but the #N/A entries are messing things up.
has any of you happened to make a function that finds the minimum NONZERO value in a data range?
(i.e. all data are numbers, all of them are positive, but some of them are zero. The function should take the excel range as input, and return the minimum value above zero).
in a column let'e say A
I have the following values
Total
11
20
7
-10
30
-5
-25
16
3
21
-8
I whant to have a variable = the row number with the minimum positive value which is in this case row number 9 and the column value 3
I want to find the minimum time value within a range of cells, excluding 0:00. Currently,
VB:
=MIN(BL5:CP5)
returns 0:00 if it exists in any of the cells.