For Every Point Above X Do Y

Mar 22, 2014

I want to pay salespeople a bonus when they sell things at a higher Gross Profit. There is a minimum markup. I want to say for every additional point above the floor (F) well pay (B*C). There is also the actual attainment filed (A). For this example lets say the floor (F) is 22% and the actual attainment is 26%.

Illustrations:
.22 = 0(B*C)
.23 = 1(B*C)
.24 = 2(B*C)
A = .26
F= .22

What I want is:
if A >= F THEN for EACH POINT above the floor add 1*(B*C)

Its actually buried in a larger IF(AND function and I need to be able to drag the function down a long column and have it scale with volume sold. But its just this bit of logic I'm hung up on.

View 2 Replies


ADVERTISEMENT

Automatically Change The Point Name To Match The Point Name In Column

Dec 10, 2008

I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.

Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range

Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))

For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "

Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub

I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class

Debugger is highlighting

Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "

View 9 Replies View Related

Map Point VBA Support

Dec 28, 2006

Does anyone know if Mappoint 2006 has automation support? I know that 2004 does. I am wanting to use the automation from excel's VBA.

View 6 Replies View Related

Find Median Point Along A Row

Jan 10, 2014

Picture 2 rows and 4 columns.

The top row is just 0, 1, 2, 3
The bottom row is something like: 850, 790, 200, 250

Here's the problem: I want to find the midpoint of the data set in row 2, based on the values in row 1. So, the sum of the data in row 2 in this example is 2090, meaning the midpoint is 1045.

If we were traversing along the top row as a line, we'd find that midpoint somewhere in the 0.3 range... but how do we get that value?

View 14 Replies View Related

Data To Right Of Decimal Point

Aug 19, 2009

is there any way to use the right function to return any data to the right of the decimal point?

eg 117.22 would be .22
108.1225 would be .1225
102.358 would be .358 etc

View 5 Replies View Related

One Entry Point Database

Aug 22, 2013

1.jpg2.jpg

I am trying to create a database for part stock but with only one entry point that is in image 1 you would select the machine and part from drop down lists and hit add. It would then lookup the corresponding machine and part and subtract 1 from the current stock database.

View 1 Replies View Related

Code To End A Main Sub Before End Sub Point

Jul 11, 2009

I’m using this code to run consecutive programs, which works really well, apart from one little glitch.

View 3 Replies View Related

UDF For Floating Point Conversion Of 32 Bit Hex

Sep 18, 2009

See here:
http://www6.uniovi.es/~antonio/uned/...-754hex32.html

This site allows me to enter a 32-bit Hexadecimal Representation and get Decimal Floating-Point result.

For instance, 4224A012 gives me 41.2.

Is there a UDF available to do this inside of Excel?

View 3 Replies View Related

Split Point Value In The Event Of A Tie

Jan 25, 2009

I am trying to split the points given to our anglers. here is my formula for assigning the points (column I) based on the place (columnH):
=VLOOKUP(H5,'Supporting columns'!A2:B33,2,FALSE). I tried adding +IF(H5=H5:H33,-1) but this affected all other rankings. if 2 anglers tie for a place, the points for that place are awarded -1.

View 3 Replies View Related

Delete All Rows Until Certain Point

Nov 8, 2007

Is there a way to delete all rows down a sheet until Cell A contains ""

or if the criteria "" would work?

i only want to keep the data below that point.

i will have to do it 11 times for different files

View 9 Replies View Related

Calculating ROI Breakeven Point

Dec 8, 2008

If I am trying to calculate the breakeven moment from a project that has an initial cost and a series of quarterly returns. This is easy to do visually but I cannot figure out how to arrive at the breakeven point from a data set such as this.

e.g. 50,000 spent at time 0
5,000 received in q1
10,000 received in q2
10,000 received in q3
10,000 received in q4
10,000 received in q5
10,000 received in q6

In which month will I break even?

View 9 Replies View Related

Can You Point A Formula To A Subtotal

Jun 12, 2009

I'd like to point a cell to a subtotal on another sheet but that sheet will periodically have the subtotals removed, more data added, then sorted and subtotalled again. Is there a way to have my other cell find the subtotal that matches a certain criterion (in this case, cost center)?

View 9 Replies View Related

Price Point Rounding

Mar 5, 2010

the rate and inventory management system my hotel uses rounds up to the next .95 currently. Most rates are based upon a 'Rack Rate' in the form of a discount percentage. So, if the Rack Rate is $129.95 and the AAA discount is 20%, the resulting $103.96 rate is rounded up to $104.95. I'm trying to fix my spreadsheet to account for the various rates and discount percentages of several different types of discounts.

I've tried a couple rounding methods:

=ROUNDUP(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .96-.99

=ROUNDDOWN(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .00-.95

=CEILING((B$30*(1-B32)),0.95) which returns some odd bits, sich as .85 and .50

So, is there a way to get Excel to shift the rounding base point from zero to .95? Or some other way to accomplish this?

View 9 Replies View Related

Return Nth Value To Right Of Decimal Point

Jan 17, 2009

Would like to do the following for our school,

In cell A1 there is a number, for instance: 12.12345

Based on that number, the 3rd number after the decimal is important.

If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ) .........

View 9 Replies View Related

Averaging From A Reference Point

Jan 18, 2007

I have a column of data and I want to average the figures from a certain point onwards.

I have the starting point value in a cell which is used as the lookup value to find the matching position in the list using the Match(lookup value, array) formula.

How do I then use that formula as the row number in a cell reference for the averageing formula?

eg Average(C(match(B1,C4:C100)):C100) - what is the correct syntax?

View 3 Replies View Related

Position A Userform At A Certain Point

Mar 22, 2007

I am trying to position a userform to my own coordinates, but I don't see how to do this in any of the form properties. I do realize that there is a startUpPosition property but none of these positions work for me.

View 4 Replies View Related

Custom Data Point

Apr 24, 2007

If I check the box labeled catagory name on the intial creation or within chart options on the right click menu each data point gets labeled with the name of the catagory.

I would like to label a significant event at one of these data points does anyone know how to do this? If this isn't a built in feature does anyone have a work around that would look good?

View 9 Replies View Related

Increment Second Decimal Point

May 12, 2007

During finalization I have to convert the numbers into millions. But due to rounding factor, the total doesnot match with the displayed fig. how can I sum only upto two digits of the fig? A sample file is attached.

View 9 Replies View Related

Have Array Point To Named Range

Aug 21, 2012

Going off this excellent thread : [URL] ....

I'm looking to replace the words in

VB : myWords = Array("dog", "cat", "hamster")

with a named range.

Here is what I did. I added the lines:

VB:
Dim myArray As Variant
myArray = Range("colorbold")

And replaced mywords with

VB:
myWords = Array("colorbold")

Something is clearly wrong.

View 3 Replies View Related

Calculate Probability To Reach At Certain Point

Jun 3, 2014

I want to know if it's possible to calculate the probability that a certain range of values, reach to a certain point.....

The range is this (example):

4
3
9
7
14
22
15
20
42
46.....

What I want to know is if this tendency will reach to 1000. Or how many steps more will be need to reach 1000.

Like I said, and don't know if this is in the right place, but if you put this values in a graphic you will see that the tendency is growing. I assume that this tendency will reach to 1000. But will be in the next 10 steps or in the next 30.

View 3 Replies View Related

Xy Scatter Graph Average Point With (+) Around It

Jun 8, 2014

I am supposed to make an xy scatter graph like the one drawn of paper. The average point need to have a "cross" around it. So far I can just plot the points. I have added the average point as secondary series. I just need to put the "+ sign or cross around" it.

View 1 Replies View Related

Add Decimal Point To Existing Number

Jan 17, 2009

I need to add a decimal point to a column of numbers. For example, where it says 126 needs to be changed to 1.26, 3035 changed to 30.35, 13593 to 135.93 and so on. Can this be done automatically or with a formula?

View 2 Replies View Related

Point To Custom Named File

Jun 15, 2009

How can i change this code to point to a file which is on my desktop which is in a folder called "cell n1" and the file is called "Cell n1"

View 2 Replies View Related

Calculating AVERAGE DEVIATION From A SPECIFIED POINT?

Jul 29, 2009

I have a set of % score values (e.g. 88%, 94%, 82%, 67%)

I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.

E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%

I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).

View 14 Replies View Related

Cells Inserting Decimal Point

Jul 30, 2009

This problem just appeared recently, as if out of the blue, and it seems to affect all of my old work sheets as well as new ones. I am using 2002 version. When I input any number in any cell the program automatically inserts a decimal point. Thus 1 becomes .1, 12 becomes 1.2, 123 becomes 12.3 etc. I have tried formatting the cells to "general" and to to "number" with no change. The thing that really gets me is that old saved worksheets exhibit this behavior as well. I can work around the problem by inputting 1. or 12. which then gives me the entry I am after but I've never needed to do this before.

View 2 Replies View Related

Check If The Number Have Two Decimal Point

Aug 20, 2009

a1 -> 2.333.33 "i want this to return as 2,333.33"

a2 -> 33.33 "i want this to retun 33.33"

a3 -> 45.555.55 "i want this to return 45,555.55"

i already asked this question and someone gave me this code -> substitute(a1,".","",1) which return to 2333.33 but the problem is for the a2 if i use the code it will return as "3333" which read as "3,333".

Is there any way that we can determine if the values have two decimal point before it will run the command?

View 3 Replies View Related

Converting Comma To Decimal Point

Aug 25, 2009

I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.

View 5 Replies View Related

Changing Raw Data To Point Values?

Dec 4, 2012

I'm looking to change raw data into a "point" system. One of my value exchanges is every $1,000 equals 5 points. So if cell A1 equals $4,685 I am looking for cell A2 to give me a value of 20. (5pts times 4 increments of a $1k) The point value only changes at the $1k milestones, so the remaining $685 does not change the point value. What would the formula look like for something like this?

View 3 Replies View Related

Allow Only In Textbox1 Numbers & Decimal Point?

Jan 31, 2014

I need to allow only in textbox1 Numbers & decimal Point.

View 4 Replies View Related

Selecting From Point X To End, And Sliding ActiveCell Over Or Down

Nov 4, 2008

I'm really sorry to keep bothering this board, but I'm trying to get all my basics down and finish up this project! I have two last issues, and I'm sure they're both simple for those of you who knows this language well. 1) Is there a way I can easily select a range from the active cell until the end of that column or row. For instance, instead of:

View 2 Replies View Related







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