IF/AND Statements In Comp Calculator

May 30, 2006

Almost done with this but I'm stumped on the last remaining formula. The
way this compensation plan will work is if reps are between 0-25% growth they
will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point.
Growth is calculated monthly vs. a monthly #. My problem is that if the rep
is at 40% they get the first 25% at $100 and the next 15% at $150. My
formula is an either or thing. The following are my formulas for 0-25%,
26-50%,51-75% respectively:

=IF(AND(D9/C9>0,D9/C9<1.25),((D9/C9-1))*100)/(B9/52)
=IF(AND(D9/C9>1.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52)
=IF(AND(D9/C9>1.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52)

View 14 Replies


ADVERTISEMENT

Multiple IF Statements (skip The Next Two Statements Or The Result Will Be Changed Again)

Nov 22, 2008

I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.

View 2 Replies View Related

If Statements; List Any Date That Has Three Or More True Statements With The Coresponding Name

May 26, 2008

Sheet one will contain the following:

DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill

On sheet two I need to list any date that has three or more true statements with the coresponding name.

EXAMPLE:
2/23/2008Phill
2/26/2008 Bill

View 4 Replies View Related

Nested IF Statements: Combine The Two Statements

Oct 14, 2009

How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?

=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)

OR

=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)

View 2 Replies View Related

Little Calculator Wanted

Jan 10, 2007

I've been trying but excel does not seem to recognise x and y

so here it is

2* X = Z

Where 2 is changable
X = Z-2

Now i just want to find what Z is.

Here's an Example
1.3x - z
1.3(x-.06) = z
1.3z - .078 = z
1.3z = z + .78
1.3z-z = .78
.3z = .78
z = .78/.3
z = 2.6

View 14 Replies View Related

Birthday Calculator

Jan 26, 2009

I am hopeless at remembering birthdays tbh - so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday.

Please see attached - I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old - but '=TODAY()-C16' yields "00 Years 4 Month(s) 12 Days"

Also, 'Leah' has just her birthday - but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())-YEAR(C4))*12),"d")'

View 6 Replies View Related

Freight Calculator

Nov 16, 2009

I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself.

Here's my conundrum:

A potential customer enters "80802" for zip code and "Solomon" for store.

StoreLocation_________ City_______ State_____ Zip______ Distance
Solomon__________ Arapahoe _______CO ____ 80802_____ 270
Garden City_______ Arapahoe_______ CO _____80802_____ 143

The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.

View 14 Replies View Related

Making A Calculator From Vb.net To Vba

Feb 11, 2008

i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;

View 14 Replies View Related

Progress Tax Calculator

Jun 19, 2006

I'm trying to come up with an efficient formulae or function to calculate tax

The problem I have is that the tax is progressive. As below

the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below

Cell B3 is my taxable amount
Cell B5 =IF(B$3>20000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$3>40000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number for the second part in cell B6. My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell, but even the simple stage defeats me at the moment.

View 12 Replies View Related

Timesheet Calculator

Dec 18, 2008

See workbook attached.

I'm looking for help to detemine rates so it automates in the sheet.

Can you give me assistance and code perhaps ? I'm pretty basic at V-Lookup and If functions. Is this the best route to take ?

All is explained within the workbook.

View 10 Replies View Related

Best Way To Implement Calculator?

Feb 20, 2014

Creating a calculator for employees and possibly customers to use to return values based on information inputted. Created a worksheet but thought there may be another way to enter and return the calculated information that may be more user friendly. New to user forms but thought that would work for the user to enter information. Can information be returned from the worksheet to the user form?

View 2 Replies View Related

Get Rid Of The Calculator Toolbar

Nov 25, 2006

I downloaded the calculator toolbar, and for some reason it doesn't look right, it doesn't resemble a calculator layout at all.

The numbers are skewed, as though the layout is in landscape view instead of portrate. I've tried repeately to delete it from the toolbars menu option, with no success. It's become annoying.

View 9 Replies View Related

12 Digit Calculator

Feb 20, 2007

Is there a way to build a 12 digit calculator in excel?

View 9 Replies View Related

Absence Calculator

Jan 30, 2008

im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 -25/12/07).

I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work.

Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence).

How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months).

I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.

View 9 Replies View Related

Age Based Calculator

Jun 7, 2008

I am trying to put together a spreadsheet that works out values based on a persons age or service length.

So in I have the following data in columns starting at B4 and ending at k4:

Date of birth; start date; text; text; text; text; Weekly Pay; Start age; Current Age; Years Service.

The formula I need to provide in l4, m4 and n4 comes from the following rules:

* 0.5 week's pay for each full year of service where age during year less than 22
* 1.0 week's pay for each full year of service where age during year is 22 or above, but less than 41
* 1.5 weeks' pay for each full year of service where age during year is 41+

So in l4 I have:

=IF(J4

View 9 Replies View Related

Billing Calculator

Aug 12, 2008

I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate.

So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined.

Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be.

View 9 Replies View Related

Golf Calculator

Feb 6, 2009

Not sure if this is do-able but I figured I would try. For a golf league coming up later this year I want to figure out how many Birdies, Pars, Bogies, Dbl. Bogies and Others each (20) golfers have during the season. After each round I would input their scores and I am looking for a program that would look at the score of the hole and the par for the hole and figure out what they got 1 under par = birdie, even par = par, 1 over = bogie, 2 over = dbl bogie and 3 over = other.

I started by creating a simple if statement but it ran out too long and my other issue is adding up the number of birdies, pars..etc for each round. Meaning a golfer can have bogies on hole #1 and #2 and the if statement can take care of that but how would get a total saying the golfer had 2 bogies.

Something like this with the - meaning a column.

birdies Pars bogies dbl bogies others total holes
4 - 5 - 3 - 4 - 4 - 5 - 6 - 5 - 8 1 4 2 1 1 9

I would do this each week and total the number of each to keep a running total at the bottom of each column. We play on the same course each week so the pars for each hole can be hard coded.

View 9 Replies View Related

Date Calculator

Oct 22, 2009

I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list:

10 days from -start date- is:
15 days from -start date- is:
20 days from -start date- is:
and so on...

I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.

View 9 Replies View Related

Staffing Calculator

May 4, 2007

I've been trying to create a Staffing calculator for a call center. Basically the calculator should be able to add up the number of agents for the next 18 intervals based on the login time that is entered by the user. I've just outlined the functioning below:

Suppose 10 agents login at 8 AM (thus logout at 5 PM), the intervals right from 8 AM till 5 PM should show up the 10 agents. Now suppose 10 more agents login at 9 AM, we would then have 20 agents logged in till 5 PM (since agents logged in at 8 will logout at 5) & the remaining 10 till 6 PM. Thus if we have 10 more agents logging in at 10:30, we will have 30 agents till 5 PM, 20 till 6 PM & 10 agents till 7:30 PM & so on. I have attached an excel file to explain the example & the way the calculator has to be built. It is preferrable that the cells containing the login time aren't fixed, but the user should be able to input any login time in any cell.

View 4 Replies View Related

Commission Calculator

Jan 22, 2008

Attached is sample data. Target payout is the amount of the base salary a salesman can earn if all goals are reached. Commissions are payed quarterly. Listed on the left are 7 goals, each with a weight of importance toward the target payout amount. The percentage achieved can only be 100% or 0%, nowhere in the middle, although they are allowed to make up a goal later if they do not make it at first. This is where I get stumped. For example, if they don't make a goal in quarter one but make the goal in quarter two plus what they missed in one, they get two quarters worth of commission in quarter 2 for that goal. I am having trouble figuring out the easiest way to go about this.

View 2 Replies View Related

Build A Working Day Calculator

Dec 4, 2009

I need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only.

I need to imput the date the invoice is received and then for the rest to be worked out automaticly

I need it to do the follwoing ....

View 11 Replies View Related

Night Payment Calculator

Feb 8, 2010

Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.

Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.

To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?

View 9 Replies View Related

Vacation Days Calculator

Dec 25, 2012

I am looking for a good and simple formula to calculate vacation days.

View 2 Replies View Related

Financial Calculator Functions

Sep 30, 2007

I would like a user friendly form where I could enter

£50 15% discount and it would add 50% plus VAT and produce POR & POC which would adjust if the suggested price was changed.

View 14 Replies View Related

Different Answers In Excel Than Calculator?

Jan 18, 2010

I have created Excel spreasheets for our payroll. The answers on my calculator are sometimes different than my spreadsheet. How can I make them tie?

View 9 Replies View Related

Quote To Sale Calculator

Jul 29, 2009

I work in sales and when under target I need to know how much more i need to sell to hit target. e.g

quotes 10 sold 1 currently at 10%

my target is 50%

at the minute i would have to work this out manually

like i need to quote and sell the next 8 to hit the 50% target.

What can i use on excel putting my current quotes and sales and my target in so it gives me the quotes and sales i need to do?

View 9 Replies View Related

CommandButton, Text Box, Calculator

Dec 28, 2009

I am trying to make a calculator inside of Excel...learning how Text Boxes work in conjunction with CommandButton. I am trying to code the button to display inside of the text box. I have never coded a text box before to do anything like that

note: For right now i am just looking at being able to click on a button [numbered 0-9] and have them dsplayed inside the text box. After that I want to be able to set up an addition, subtraction, etc button to actually have the math done.

View 9 Replies View Related

UK Bank Holiday Calculator

May 2, 2006

I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!!

I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.

View 3 Replies View Related

Bank Interest Calculator

Dec 1, 2006

I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)...

Public Function BankCalculator()
'Bank Calculator for different accounts
'declare variable
Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double
Set shtBank = Application.Workbooks("Bank Calculator.xls")
'input box for amount of money, assign address
intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................

View 4 Replies View Related

Create A Football Calculator

Jan 15, 2007

i m just trying to create a football calculator, just a real basic one, my excel is ok. Just doin it for a mate as he is arranging some charity football event. i have inputted the teams and that and worked out goal differences but would like to calculate games played, wins losses draws and points, can someone advise or quicky have a look, see attachment.

View 6 Replies View Related







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