Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


AbbeyG

3 posts

Wannabe Geek


#66103 12-Aug-2010 11:51
Send private message

Morning all,

Im after some help for an assignment im doing.
While it may not be the most effective way to work it out, it's what i'm required to do.

I have to calculate interest on a loan amount where the interest rate varies between each level of $$ borrowed. Similar to how tax is calculated.

I have done this for my nested if statement, but the second question is how to do it using a vlookup.
My tutor told me I will be using if statements inside it.

$0-$12,000 borrowed is 5%. $12,001-$45,000 is 7.5%, $45,000-$80,000 is 8% and above $80,000 is 8.5%

My if statement is as follows: Referencing back to Cell B8

=IF(B8<12000,B8*0.05,IF(B8<45000,(B8-12000)*0.075+(12000*0.05),IF(B8<80000,((B8-45000)*0.08)+((45000-12000)*0.075)+(12000*0.05),IF(B8>=80000,((B8-80000)*0.085)+((80000-45000)*0.08)+((45000-12000)*0.075)+(12000*0.05),0))))

This works very well.

But how on earth do i get this into a table with only the % showing?

Thanks in advance for any help.
(I am also interested in the best way to do this for future referance too)

Create new topic
AbbeyG

3 posts

Wannabe Geek


  #366873 12-Aug-2010 17:18
Send private message

Ok so Ive managed to get what I want done, but its not nearly as tidy as i had hoped.
I added a third column with the if statements in it, and used the second as the %.
Just ment i referenced the 3rd column instead of the second in the vlookup.

Still very interested if anyone knows a cleaner way to do this.



rvangelder
352 posts

Ultimate Geek


  #366928 12-Aug-2010 19:39
Send private message

A1 to A4 are:
0, 12000, 45000, 80000
B1 to B4 are:
5.0%, 7.5%, 8.0%, 8.5%
C1 is 0
C2 is a formula:
=(A2 - A1) * B1 + C1
Copy C2 to C3
Copy C2 to C4

Formula:
=(B8 - VLOOKUP(B8, $A$1:$A$4, 1)) * VLOOKUP(B8, $A$1:$B$4, 2) + VLOOKUP(B8, $A$1:$C$4, 3)

Look mum, no IFs!

AbbeyG

3 posts

Wannabe Geek


  #373383 26-Aug-2010 22:11
Send private message

Thanks rvangelder,

I had to use the If's as my tutor required it....
But thanks so much for your response.

I'll have a go using it to see if it works too :)


Create new topic





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.