Nested IF Function help

N

Natalie

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
 
L

Luke M

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)
 
R

ryguy7272

Let’s 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"})
 
N

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..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top