Hello,

I have 2 tables, 1 with amount of credit due (bottom table) and I want to be
able to enter the number of minutes in downtime (top table) and return the
right amount of credit due back in the adjacent field.. Should be a nested if
but I can't get it to work..

Period Downtime in Mins
Jan 10
Feb 0
Mar 0
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0


Outage times in mins % SC Due MRR Credit due
0-43 0% £23,536.99 £0.00
44-216 20% £23,536.99 £4,707.40
217-432 35% £23,536.99 £8,237.95
433-4320 50% £23,536.99 £11,768.50
4320+ 75% £23,536.99 £17,652.74

--
Natalie

In your bottom table, change the first column to the cutoff points, like this:
0
44
217
433
4321

Your formula in the other table then becomes something like:
=LOOKUP(B2,'Data sheet'!A$2:A$6,'Data sheet'!D$2:D$6)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Natalie" wrote:

Hello,

I have 2 tables, 1 with amount of credit due (bottom table) and I want to be
able to enter the number of minutes in downtime (top table) and return the
right amount of credit due back in the adjacent field.. Should be a nested if
but I can't get it to work..

Period Downtime in Mins
Jan 10
Feb 0
Mar 0
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0


Outage times in mins % SC Due MRR Credit due
0-43 0% £23,536.99 £0.00
44-216 20% £23,536.99 £4,707.40
217-432 35% £23,536.99 £8,237.95
433-4320 50% £23,536.99 £11,768.50
4320+ 75% £23,536.99 £17,652.74

--
Natalie

Lets say the months are in Col A and the numbers are in Col B, put this in
cell C1:
=LOOKUP(B1,{0,44,217,433,4320},{"£0.00","£4,707. 40","£8,237.95","£11,768.50","£17,652.74"})


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Luke M" wrote:

In your bottom table, change the first column to the cutoff points, like this:
0
44
217
433
4321

Your formula in the other table then becomes something like:
=LOOKUP(B2,'Data sheet'!A$2:A$6,'Data sheet'!D$2:D$6)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Natalie" wrote:

Hello,

I have 2 tables, 1 with amount of credit due (bottom table) and I want to be
able to enter the number of minutes in downtime (top table) and return the
right amount of credit due back in the adjacent field.. Should be a nested if
but I can't get it to work..

Period Downtime in Mins
Jan 10
Feb 0
Mar 0
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0


Outage times in mins % SC Due MRR Credit due
0-43 0% £23,536.99 £0.00
44-216 20% £23,536.99 £4,707.40
217-432 35% £23,536.99 £8,237.95
433-4320 50% £23,536.99 £11,768.50
4320+ 75% £23,536.99 £17,652.74

--
Natalie

I ended up using a vlookup and using 'true' so as to find nearest match but I
did change my bottom table as Luke suggested..
--
Natalie


"ryguy7272" wrote:

Lets say the months are in Col A and the numbers are in Col B, put this in
cell C1:
=LOOKUP(B1,{0,44,217,433,4320},{"£0.00","£4,707. 40","£8,237.95","£11,768.50","£17,652.74"})


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Luke M" wrote:

In your bottom table, change the first column to the cutoff points, like this:
0
44
217
433
4321

Your formula in the other table then becomes something like:
=LOOKUP(B2,'Data sheet'!A$2:A$6,'Data sheet'!D$2:D$6)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Natalie" wrote:

Hello,

I have 2 tables, 1 with amount of credit due (bottom table) and I want to be
able to enter the number of minutes in downtime (top table) and return the
right amount of credit due back in the adjacent field.. Should be a nested if
but I can't get it to work..

Period Downtime in Mins
Jan 10
Feb 0
Mar 0
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0


Outage times in mins % SC Due MRR Credit due
0-43 0% £23,536.99 £0.00
44-216 20% £23,536.99 £4,707.40
217-432 35% £23,536.99 £8,237.95
433-4320 50% £23,536.99 £11,768.50
4320+ 75% £23,536.99 £17,652.74

--
Natalie