Select Numbers After Every 3 Distance From The Target Number

Jun 19, 2009

Below shows a example table:
*ABCDEFGHIJKLMNOPQRSTUVWXY11458912131415192122232728293234363741444548492314

The table above has numbers from A1:Y1. The blue number is the target number, which is selected in cell H3. I want selected numbers from the target number H1 going along each 3 numbers on the right to get the number 22 which is after the 3rd distance. For example the number would be 22, 29, 37 and 48, which are all in red. After the number 48 it should start counting from the beginning and reach to the target point H1. In this way it will select 5 and 13 also. In total it would be 6 numbers selected. The target point can be selected from A1:Y1 but in this example case is H1.

The table below shows the final 6 numbers, which are obtained after the results along each 3rd distance numbers.
*AAABACADAEAF1222937485132

View 9 Replies


ADVERTISEMENT

Counting Distance Between Number 1

Mar 27, 2014

I need a formula, from which I need to drag (if needed) and to fill with numbers, by following principles:

- when the left cell contain the zero, it needs to find on the same column if there exists another zero, or another one and count them, let's say if find three zeros untill find number one, return 3.
- when find a number 1, after another 1, has to return a zero.
- the formula need to initialise from the top of the column, only finds the first number 1.

I have a relevant example down bellow. I used to count them visually and sometimes errors sneak into my spreadsheets.

View 5 Replies View Related

Select Range In Target.address

Feb 10, 2010

I have this simple requirement

Business ProcessBp1Bp2Bp3Bs. Consulting

The Bs. consulting can take any values from business process like Bp1 or Bp1 or Bp2...

for this i have written validation macro

If Target.Address = "$C$21" Or Target.Address = "$D$21" Or Target.Address = "$E$21" Or Target.Address = "$F$21" Or Target.Address = "$G$21" Or Target.Address = "$H$21" Then

do the validation;

end if;

this works fine, but i need to use or condition , is there any better way to write this, a more compact way,

View 9 Replies View Related

Target.offset To Select Ranges

Mar 27, 2007

i'm having a really hard time figuring out how to use target. offset to grab a range of cells. For Example:

I am currently running a macro that searches column "D:D" for data. When the data is found, i want to be able to Grab columns D to AG for that row, and apply conditional formatting. It seems like using Target.Offset should be able to accomplish this, but i can't figure out how to specify a range using Target.Offset. Currently, i have to specify each cell i'd like to select, and apply formatting one at a time, which has bloated my code considerably (and causes me to run out of room very quickly).

View 4 Replies View Related

Capture A Target Number

Dec 12, 2009

I have a number result in a cell (d1) from a calcuting formula elswhere on the spreadsheet.

As the worksheet is constantly calculating the result in d1 keeps increasing and decreasing

I would like a formula in cell f3 that will give the result 1 if the d1 has reached the number 45. If cell d1 has not had the number 45 appear f3 will be 0

View 9 Replies View Related

Calculate Number To Reach Target

Dec 7, 2007

I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red.

In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.

View 9 Replies View Related

Command For Target: Send A Target Link To A File

Sep 1, 2007

i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.

View 2 Replies View Related

Utilize A Select Case Statement In Target Intersect Statement

Jul 28, 2009

I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.

My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.

I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?

View 5 Replies View Related

Calculate Number Required To Achieve Target Percentage?

Mar 28, 2012

I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.

A = Departments
B = Calls Dropped
C = Calls Received
D = Current % (C/F)
E = Target %
F = Total (B + C)
G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)

Departments
Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1
21083.33%95.00%12 Dept 2
067100.00%90.00%67 Total
27791.67%95.00%79

View 7 Replies View Related

Number Of Promoters Needed To Achieve Target Greater Than 75%

Oct 5, 2013

I need to get right formula for below details:

Promoters
Neutral
Detractor
no.of Surveys
Percentage
Target
Promoter
needed >75%

Smith, John
5
2
1
8
50.0%
75%
???

Formula to get Percentage: =(Promoter-Detractor)/Neutral

Need to know how many numbers of Promoters needed to get 75.00%

View 1 Replies View Related

Formula For Number Of Perfect Ordered Needed To Achieve Certain Target

Jun 11, 2014

I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.

Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points

Grade are evaluate by:
<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent

Total points / total orders = final grade (or percentage)

As of now i got : 53800 (points) / 624 (orders) = 86.22(%)

Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.

Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.

Amazon seller rating calculator.xlsx‎

View 3 Replies View Related

Select From Multi-Select Listbox And Get Index Number?

Oct 30, 2012

I have a multiselect listbox with values that gets populated from a sql statement, and I would like to get is the first or second index from the selected item. I know how to get the listindex from a combobox by using:

VB:
cbnumber.List(.ListIndex, 0)

How can I loop through and get the 1st index number for the selected items only from the listbox? I want to pass this index number to another sql statement.

View 5 Replies View Related

Select An Array Of Numbers And Once Those Numbers Are Selected

Jun 19, 2008

I need to know if there’s a function (or method) in excel that’ll allow me to select an array of numbers and once those numbers are selected, to use only the numbers (for an averaging function) that yield the best sigma?

View 9 Replies View Related

Automatically Populate Data From Target B Worksheet Into Target A Worksheet

Dec 3, 2012

I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.

View 2 Replies View Related

Find Value Of Target On Same Row As Target

Jun 4, 2008

I am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.

View 2 Replies View Related

Select Last Number In A Row That Contains Number Greater Than Zero And Return A Value

Feb 14, 2014

[Code] ......

How to create a formula in cell M2 that looks at the second row of numbers (0 and 500s) and tells me the rightmost value that is greater than zero. In this example it would return 12.

Second Question: Is there a way to return any counted value, for example the second-to-last number that is over 0?

View 2 Replies View Related

Generating Random Numbers Or Set Of Numbers Which Add Up To Certain Number?

Oct 24, 2013

I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.? How can I achieve this.

View 14 Replies View Related

How To Select 5 Numbers From 1 To 90 Numbers

Mar 29, 2014

i need a chat for 590 lotto.how to select 5 numbers from 1 to 90 numbers.80-75-71-13-18. how can i select the next 5 numbers

View 4 Replies View Related

Formula To Select Order Numbers

Feb 15, 2012

I would like to create a formula that will select a 7 digit order number that begins with an 8 or a 9. For example in cell "AI3" I have the following:112972/8517223.

I would like cell "AH3" to capture only the number 8517223. Also, instead if that cell had 9000954 if would like "AH3" to have that number as well. In addition, if cell "AI3" was blank, I would like the formula to also search in columns "AJ3", "AK3" and "AL3" for a seven digit number that begins with a 8 or 9.

View 3 Replies View Related

Select Cell With Numbers In From Range Of Cells Containing Words

Mar 26, 2014

I have a column with rows from 41:254, the first cell contains a word, and the next is blank, and this repeats the whole way down.

a previous macro i run changes one of the cells in the range (one of the ones containing a word) to a number value, i want to write a further macro to select this cell that has now changed. when it changes it will be the only cell in the range with a number in.

View 4 Replies View Related

Data Valuation To Select Lane Numbers For Zones?

Jun 13, 2014

I am trying to create a list where the user will select which "Lanes" to assign to "Zones" -- for example... Zone 1 will be Lanes 1 through 10. Zone 2 will be Lanes 11 through 25. Etc, etc... I want to make it very easy for a user to change which lanes are assigned to which zone. I thought about using data valuation but I need Zone 2 to automatically bump up the *start* lane if you change the end of Zone 1.

Example: Zone 1 is set at Lanes 1-10, but I want to extend it to Lane 12. How can I make it so that Zone 2 automatically starts at Lane 13 after I set Zone 1? I would like to use drop-down menus for this because it seems like that would be the most user-friendly.

View 9 Replies View Related

Distance Between Coordinates?

Dec 8, 2013

I have created a sheet that shows distance between coordinates. Also working on something to put them in order of the closest point. I used a fixed point to find the nearest coordinate, but since i'm using a fixed point it doesnt go the order I want it to. I have attached the excel file I am working on.

View 11 Replies View Related

Distance Between Zip Codes

Feb 26, 2009

I have two columns, zipA and zipB. There are over 7000 rows and I need to calculate the distance between zipA and zipB for each row (so the result should be over 7000 rows of distances (in miles)). I posted this in the programming board because I figured it would require some. I don't have any add-ons.

View 14 Replies View Related

Distance Between 2 Geo-points

Mar 14, 2009

Need a direction for this type of calculation.

View 2 Replies View Related

Distance Between 2 Zip Codes

Feb 24, 2007

At my last job we purchased a zip code addin from spheresoft that could give you a distance between two distance ie: =ZipCodeDistance(zip1, zip2)

This worked well but I am now at a new job and wondering if there is any data out there that could help me build my own sheet. I just need California

I have both zip codes now just need the distance, I could sit down, drink a ton of coffee and enter zip codes all night into Mapquest but am hoping for a better way.

View 9 Replies View Related

Calculating The Distance Round An Arc

Jan 27, 2009

I am looking to calculate the distance around an arc, given A-B = 70 and the depth is 70cm.

View 10 Replies View Related

Lookup & Find The Distance

Apr 2, 2009

I have a distance grid with place names down column A and along Row 1 with distances in the middle. I then have a place name in cell A1 on sheet2 and a distance in cell B1 on sheet2. Ideally I want cell C1 on sheet2 to lookup the place in cell A1 on Row1 of the grid on sheet1 and then find the distance in B2 in that column before displaying the correspoding value from Column A on sheet2. I've tried various Match and Index functions with no success.

View 5 Replies View Related

Calculating Miles, Distance & MPH

Jul 7, 2009

My wife wanted a excel sheet to track her walking. See Attached. I think I have the time calculations wrong, for example I am entering 1.07 for an hour and 7 minutes.

View 2 Replies View Related

Minimum Distance Between Points

Jul 24, 2009

I want to know the minimum distance between customers that I visited in each day.

If i visit customer 1, 2 and 5 in one day, which distance is smaller? 1-2, 2-5 ou 1-5 and what is the corresponding value?

I have in columns DATE, CUSTOMER 1, CUSTOMER 2, ..., CUSTOMER 5 and in each row I put the Day and 1 or blank for each customer (visited / not visited). I also have a mileage chart for each customer in another sheet which is the source for my distance data.

I tried INDEX and MATCH but all I get is ZERO... I think it is comparing the same CUSTOMER.

View 14 Replies View Related

Distance Between 2 Cells With Walls?

Jan 20, 2013

I am wondering if there has been something made that is able to measure the distance between 2 cells if there is a barrier between them. For example, say you want to measure the distance between A1 and C1. If there were no barriers, the answer would be 2 cells, but say there was a barrier (a wall) that ran from B1 to B3. The distance would now be 8 assuming you cannot move diagonally, or 6 if you can move diagonally.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved