Nesting If & if(and statements

[RCorrigan]

Ok --- Brain Freeze

I know how the standard "Grading a Test" nested statement works --
=IF (B4<=44,"F", IF(B4<=54,"D", IF(B4<=74,"C", IF(B4<=89,"B" ,"A" ))))

What I am trying to do is nest a group of if(and statements

Is this possible even??

This -- =IF(AND(E17="Business", G17="Home",((I17-H17)>9.4)), ((I17-H17)-9.4)*1.609, (I17-H17)*1.609) works __Horray

BUT ... I need to mess with the E17 and G17 cells and the (I17-H17) calculation

so .. I have three scenarios
1) if Business & Home & Greater than 9.4 then total less 9.4 or else just total
2) If Business & Home & Less than 9.4 then Zero or else zero
3) If Home & Business & Less than 9.4 then Zero or else zero

And would like them into one cell calculation.

Many Thanks in Advance

<Do I need A Signature or will an X do?>

 

[SkipVought]

Well if they all must be in one calculation, then you cannot have three SEPARATE scenarios. The three must work together similarly...
[tt]
if Business & Home & Greater than 9.4 then total less 9.4 else
If Business & Home & Less than 9.4 then Zero else ??????
[/tt]
Unless you intended some other logic.

BTW, scenarios 2 & 3 are identical!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RCorrigan]

Skip

It's for a mileage claim sheet.

Scenario 1 (should) is if trip is type "Business" & the end destination is "Home" & the journey is > than 9.4 miles then claim total less 9.4 else just claim the total.
i.e. If the last trip of the day is to home and is over 9.4 miles then the amount in the claimed box is the total less 9.4 or if it's not the last trip then the claimed amount is just the total.

The other two should be if the trip type is business and you are starting or ending from home and the trip is less than 9.4 miles then the claimed amount is zero.
If the first / last trip is totally un-claimable the type is "To /From Work" so it is excluded from the total anyway !

I started this late at night, hence the brain freeze, so there might be an easier way to do it.

Many Thanks for looking !

Cheers
Richard

<Do I need A Signature or will an X do?>

 

[SkipVought]

Well then that starting point data needs to be in your if test and it isn't!

A and B is equivalent to B and A!

Where is this data in your sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RCorrigan]

Skip

I have a "Purpose" Column E - if the description is Business then the total column J is added up ( =SUMIF(E7:E34,"=Business",J7:J34))

I then have From and To Columns, F & G respectively
Columns G & I are start Miles & end Miles -- Column J is the total miles (converted to km's by *1.609)

So I have

A B C D E F G H I J
N/A Date Time Desc. Purpose From To Start End Kms
--- 27th 9am T/F Work Home Work 10 19.4 15.1 ---- NOT added to Total
--- 27th 10am Business Work IBM 19.4 44.1 39.7 ---- IS added to Total


So if the next journey is as follows
--- 27th 11am Business IBM WORK 44.1 68.8 39.7 ---- would be added to the total

But if it is

--- 27th 5pm ???? IBM HOME 44.1 68.8 **** you have to take 15.1km / 9.4m OFF the mileage that can be claimed

Also if the next day the journey was
---- 28th 9am ???? HOME IBM 68.8 93.5 **** again you'd have to take off the first 15.1km/9.4m from the amount that can be claimed.


Hope that makes some sense !!!!

Cheers
Richard

<Do I need A Signature or will an X do?>

 

[SkipVought]

So what is the Purpose logic? If To is "Home" then don't add to total else add to total?

Examples are helpful to clarify logic, but examples do not define the logic.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RCorrigan]

Skip,

The Purpose column was to separate Business from T/F Work trips - but then ran into the half and half trips !!!!

Cheers
Richard

<Do I need A Signature or will an X do?>

 

[SkipVought]

That was a Reason. That was not at all helpful toward defining EXACTLY what the LOGIC is that defines business and not business.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RCorrigan]

Employee Joe Public has a normal place of work, the office.

If on Monday he is based at the Office all day, he makes two trips
1) From Home to Office (15km)
2) From Office to Home (15km)
both these trips are not claimable

If on Tuesday he makes four trips,
1) From Home to Office (15km)
2) From the Office to Company B (35km)
3) From Company B to the Office (35km)
4) From Office to Home (15km)
then trips 2 & 3 are claimable, but trips 1 & 4 (as before) are not.

If on Wednesday he makes three trips
1) From Home to Office (15km)
2) From Office to Company C (40km)
3) from Company C to Home (35km)
then trip 1 is not claimable (as Before), trip 2 is claimable (as Before) but for trip 3, Joe can claim for 20km - this represents the portion of the trip over his normal 15km trip to and from his place of work.

If on Thursday he makes 2 trips,
1) from Home to Company D (10Km)
2) from Company D to Home (10Km)
then neither trip is claimable as both fall below the 15km he would normally travel

If on Friday he makes 3 trips,
1) from Home to Company C (35km)
2) from Comapnay C to the Office (40Km)
3) from the Office to Home (15km)
he can claim the 20Km portion of trip one (i.e. that part which is over his normal daily commute), trip 2 is also claimable in it's entirety, and trip 3 is not claimable.

Does that help ??

Cheers Richard

<Do I need A Signature or will an X do?>

 

[SkipVought]

these are EXAMPLES ONLY. First time we hear about a 15km minimum!

LOGIC: Trips are only chargeable when the distance is greater than 15km.

Thats LOGIC!

So what's the logic for a business trip?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RCorrigan]

No.
If Joe travels to the Office (his normal 15km) and then does 99 trips to and from other businesses before going back to the office, ALL of that mileage is claimable.
The restraints (for want of a better word) only apply if the trip "conflicts" with what would normally be a trip to / work.

I can see the logic - and have tried to explain through use if examples as that seemed the easiest way.
The excel logic escapes me and it seems to need either nested do-dahs or formulas I have never used before.

Cheers
Richadr

<Do I need A Signature or will an X do?>

 

[bluedragon2]

So, you basically have four variables for the logic:

A - To
B - From
C - Distance from Work to Home
D - Trip Distance

So, your logic should be:

If A OR B = Home AND D>C then Claim D-C
IF A OR B = Home AND D=C then No Claim
IF A AND B = Business then Claim D

An Excel Formula would look like:

=IF(OR(A1="Home",B1="Home"),IF(D1>C1,D1-C1,0),D1)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[zelgar]

Maybe something like this

=IF(J5>15,IF(OR(F5<>"Home",g5<>"Home"),J5-15,J5),0)

 

[mintjulep]

You could simplify things for yourself if you break the problem down to four parts.

Part 1: Trip can be claimed or not. 1 or 0
Part 2: Distance requires adjustment or not 1 or 0
Part 3: Required Adjustment a number
Part 4: The distance driven a number

Then

Part 1 * (Part 4 - (Part 2 * Part 3))

Next recognize that Excel treats Boolean TRUE as 1 and FALSE as 0 in algebraic calculations, so you don't need IF(), only AND() and OR().

Still, the number of tests for Part 1 is too high to make a reasonable one line formula. I count six cases.

A UDF would be much cleaner.

 

[RCorrigan]

Cheers Guys, I'll have a play when I get two seconds

Richard

BTW - if there is a way to do it that means I have to change / add / not use columns, then that's fine too !!!!

<Do I need A Signature or will an X do?>