Posts by etaf

• Calculate 20% and/or 40% Tax depending on salary

Re: Calculate 20% and/or 40% Tax depending on salary

=IF(AND(\$O\$7>\$R\$5,\$O\$7<=\$S\$5),(\$O\$7-\$S\$4)*0.2,\$T\$5*0.2)

so that test to see if sal > 34371 and less then 150000
which it is 36000
then you do
(\$O\$7-\$S\$4)*0.2
which is the difference of the 34371 and the Sal
BUT that should be @ 0.4 not 0.2
and if false you do this \$T\$5*0.2 - not sure why ?

if its not greater than 34371- then false would = 0

• Error in Excel Formula

Re: Error in Excel Formula

thanks for letting us know

you still dont have a condition for the last FALSE element

to mark your thread as Solved, you can do the following -

Incase your issue is not solved, you can undo it as follows -

• Error in Excel Formula

Re: Error in Excel Formula

what are we reviewing for
you seem to be using and as i posted

you need to add the final false statement

if you add my formula - you will see that none of your conditions apply

q22 - does not allow the yes or no ?

• Error in Excel Formula

Re: Error in Excel Formula

you need to clarify what you are trying to do as requested by exceleb

looks like you are looking for an and
=IF(Q22="Yes",Q149="Existing",SUM(Q29+45),IF(Q22="NO",Q149="Existing",SUM(Q29+1),IF(Q22="NO", Q149="NEW",SUM(Q29+365),IF(Q22="NO",Q149="Transfer",SUM(Q29+90))))

so if Q22 ="yes" AND Q149 = "existing" then do SUM(Q29+45)

=IF(AND(Q22="Yes",Q149="Existing"),SUM(Q29+45),IF(AND(Q22="NO",Q149="Existing"),SUM(Q29+1),IF(AND(Q22="NO",Q149="NEW"),SUM(Q29+365),IF(AND(Q22="NO",Q149="Transfer"),SUM(Q29+90),"What todo if conditions do not exist"))))

but YOU need to add a false - if all these do not work

• Conditional formatting dates in excel

Re: Conditional formatting dates in excel

you would put those formulas into conditional format and change the fill colour

for the blank cell, there is a difference between a blank cell and an empty cell, but this will work on both
= Cell = ""
format Red

then additional formulas for the date
=IF( TODAY() - 20 <= CELL, TRUE,FALSE)
not sure what you are doing with workday - if you could explain

• Perform multiple calculations when a value goes over a threashold

Re: Perform multiple calculations when a value goes over a threashold

• Sheet Name as Variable

Re: Sheet Name as Variable

you maybe able to use indirect and use the cell value in column b

=INDIRECT( "'[data.xlsx]"&B2&"'!\$l\$10")
then when you copy down the sheet B2 will change to B3,B4 - which will call sheet 01,02, 03 etc

OR you can use it in the formula

• Prob in sorting blank cells result of formulas in a range of pos and neg cell

Re: Prob in sorting blank cells result of formulas in a range of pos and neg cell

you will need a macro to replace the empty string "" with a NULL
if you do a isblank() on the empty cell you get a false on a null cell you get true

so i think vba is the only solution,and i'm not great on vba - hopefully someone will reply with a vba solution soon

• Prob in sorting blank cells result of formulas in a range of pos and neg cell

Re: Prob in sorting blank cells result of formulas in a range of pos and neg cell

how are you sorting ? largest to smallest - blanks "" will go to the top - smallest to largest - blanks "" goto bottom

what version of excel

• TRYING TO MAKE A BONUS COMMISION PAYOFF calculation USING A GRAPH (VLOOKUP POSSIBLE?)

Re: TRYING TO MAKE A BONUS COMMISION PAYOFF calculation USING A GRAPH (VLOOKUP POSSIB

your welcome - the link i posted does try an explain how the formula is laid out - not that well - but a few reads and you should get it
if you do screw up - just ask again for clarification here

• Perform multiple calculations when a value goes over a threashold

Re: Perform multiple calculations when a value goes over a threashold

i think this formula should do what you are after - do a little bit of testing

=IF(SUM(K\$3:K4)<=10000,K4*H\$3,IF(SUM(K\$3:K3)>10000,K4*I\$3,((10000-SUM(K\$3:K3))*H\$3)+(K4-(10000-SUM(K\$3:K3)))*I\$3))

in L3 the 1st entry just use miles*rate =K3*H3
then in L4 - put the formula and copy down

=IF(SUM(K\$3:K4)<=10000,K4*H\$3,IF(SUM(K\$3:K3)>10000,K4*I\$3,((10000-SUM(K\$3:K3))*H\$3)+(K4-(10000-SUM(K\$3:K3)))*I\$3))

the 1st section is testing to see if the total mileage including this periods is less than or equal to 10000 - if so then use the H3 rate

=IF(SUM(K\$3:K4)<=10000,K4*H\$3,IF(SUM(K\$3:K3)>10000,K4*I\$3,((10000-SUM(K\$3:K3))*H\$3)+(K4-(10000-SUM(K\$3:K3)))*I\$3))

the 2nd section is testing is the mileage upto last period more than 10000 - if it is then this period will be more than 10000 so use the I3 rate

=IF(SUM(K\$3:K4)<=10000,K4*H\$3,IF(SUM(K\$3:K3)>10000,K4*I\$3,((10000-SUM(K\$3:K3))*H\$3)+(K4-(10000-SUM(K\$3:K3)))*I\$3))

the 3rd section must therefore be across 10000 and the mileage should be at the two different rates
((10000-SUM(K\$3:K3))*H\$3)
takes 10000 away from the mileage upto last period - and so works out how many miles this period are at the < 10000 rate
(K4-(10000-SUM(K\$3:K3)))
takes the miles for this period and takes away from the 10000 - the mileage upto last period to work out the miles over 10000

I have played with your sample - and just added miles - so column K does not have your formula in all cells

• NESTED IF formula with DATE FUNCTION

Re: NESTED IF formula with DATE FUNCTION

well for some reason march is not correct
=IF(G\$5<=\$E6 which is true and so should return
[TABLE="width: 64"]

[tr]

[TD="width: 64, align: right"]41040[/TD]

[/tr]

[/TABLE]
6840 * 6

the test for the dates is not working
i copied into a new cell and not working

when i re-enter the dates and values in a different section it works

for march it is doing a6*4

but March is less than contract due date

31/3/13 is less than 27/8/13

cant seem to make it work in your sheet - but can on a separate entry

see d20 and g20 and g22

tested if text and used values - copied formula into notepad and reentered

i'll have another play

• Perform multiple calculations when a value goes over a threashold

Re: Perform multiple calculations when a value goes over a threashold

can you provide a sample spreadsheet
also i dont fully understand what are you are trying to do

Quote

as an example if the limit is 100 and the running total is at 98, this months total is 10, the fisrt 2 would need to be multiplied by x and the remainder multipled by y and added together.

not sure what the first 2 refers to - or the remainder of what

need a further detailed explanation and examples for me to be able help here

• Find lowest value in column B for each customer in col A w/multiple records per custm

Re: Find lowest value in column B for each customer in col A w/multiple records per c

you should be able to use a pivot table to do that easily

also
assuming the customer ID is in column A - starting A2
and the values to find the minimum are in column B starting B2

in cell d2 put
=INDEX(\$A\$2:\$A\$38, MATCH(0, COUNTIF(\$D1:D\$1, \$A\$2:\$A\$38), 0))
use control+shift+enter to make an array formula - {} appear around the formula
copy down D
this will extract a set of unique IDs for all customers in column A
now in E2
put
=MIN(IF(A2:A38=D2,B2:B38))
this will calculate the minimum for each unique customer ID in D
copy down E

see attached

we can also add error correction to remove the 0 and #n/a if this works for you

so that covers the minimum value by customer

now a little confused on the rest

Quote

Find lowest value in column B for each customer listed from among many records for each customer and Identify which record has the least value

sounds like the same thing here - Lowest value and least value by customer

Quote

Order # of column C had the least value

is this by customer or lowest value in the whole list and the order number - simple Min and then a vlookup would do that for order # and flag

but confirm your exact requirements before i spend any further time on something thats not required

• TRYING TO MAKE A BONUS COMMISION PAYOFF calculation USING A GRAPH (VLOOKUP POSSIBLE?)

Re: TRYING TO MAKE A BONUS COMMISION PAYOFF calculation USING A GRAPH (VLOOKUP POSSIB

i have put the lookup values in sheet 2
A1 = \$ Amount sold
B1 = Qty

and in c1
=IF(ISERROR(INDEX(Sheet1!C3:L52, MATCH(B2,Sheet1!B3:B52,1), MATCH(A2,Sheet1!C2:L2,1))),0,INDEX(Sheet1!C3:L52, MATCH(B2,Sheet1!B3:B52,1), MATCH(A2,Sheet1!C2:L2,1)))

I have also used the nearest so
7999999 will use the 7000000 column

this is an explanation of how the grid lookup works
http://skp.mvps.org/xl00002.htm