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
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 -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
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 ?
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
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
Re: Perform multiple calculations when a value goes over a threashold
your welcome
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
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
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
perhaps a sample spreadsheet with dummy data that reproduces your problem
what version of excel
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
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
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"]
[TD="width: 64, align: right"]41040[/TD]
[/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
must be going made
i'll have another play
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
Quoteas 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
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
QuoteFind 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
QuoteOrder # 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
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
