Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-26-2014, 08:30 PM
AUHAMM AUHAMM is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2014
Posts: 2
AUHAMM is on a distinguished road
Default I need to add multiple values based on multiple criteria in a cell not sure what to do


Ok so I basically need to make two separate if statements for a column of data and i have no idea what i am doing. The spreadsheet is about how much students can earn on a scholarship based on their GPA and experiential activities. for data column B i have an initial offer of $29,705 for every student, column C displays the GPA and column D the Experiential activities(numerical value). I need a function for column E that adds 2,000 to the initial offer if the GPA is greater than 3.5, +1,000 if greater than 3.0 and +500 if greater than 2.5 and also based on the experiential activities adding 3,500 if activities greater than 4.5, +3,000 if > 4.0, +2,500 if > 3.0, +2,000 if >2.0 and +1,000 if > 1.0.

so for example;

Xavier got a GPA of 3.6 and a 2.1 on experiential activities so based on the criteria he should have 2,000 added to the initial offer for his GPA and an additional 2,000 added for his experiential activities resulting in his highest offer being 33,705.

initial offer:29,705 +GPA bonus:2,000 + Experiential activities bonus:2,000= 33,705
Reply With Quote
  #2  
Old 10-26-2014, 11:33 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,648
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Assuming the values are on row 2, the following formula will calculate the bonuses:
=500*(C2>2.5)+500*(C2>3)+1000*(C2>3.5)+1000*(D2>1) +1000*(D2>2)+500*(D2>3)+500*(D2>4.5)
To have the full value calculated instead of just the bonus, add +29705 to the formula:
=500*(C2>2.5)+500*(C2>3)+1000*(C2>3.5)+1000*(D2>1) +1000*(D2>2)+500*(D2>3)+500*(D2>4.5)+29705
Simply copy down as far as needed.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 10-27-2014, 08:37 PM
AUHAMM AUHAMM is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Oct 2014
Posts: 2
AUHAMM is on a distinguished road
Default

Thank you this worked perfectly! I am a little confused on how though. could you explain how I got the numbers I needed by using *500 and *1000? I am not sure what is going on here in general. Sorry for my lack of experience
Reply With Quote
  #4  
Old 10-27-2014, 09:11 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,648
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

OK, if we just take the first part of the formula, you'll see:
=500*(C2>2.5)
The (C2>2.5) is a true/false test that, in a formula like this, returns 1 if true and 0 if false. If C2 is greater than 2.5, (C2>2.5) returns 1, which becomes 500*1, which equals 500. If C2 is not greater than 2.5, (C2>2.5) returns 0, which becomes 500*0, which equals 0.

The overall formula is just a sequence of the above expressions, each one adding to the total of whatever has already been returned. So, since 3.2, for example, in C2 is greater than both 2.5 and 3, you get 500 for (C2>2.5), plus another 500 for (C2>3), which equals the required 1000 for (C2>3).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Multiple Rows to new workbook when multiple criteria is met. flds Excel Programming 5 09-30-2014 09:58 AM
Insert values from multiple rows based on value in one column pachmarhi Excel 3 07-18-2014 09:57 PM
Change values in cells based on criteria SaneMan Excel Programming 2 02-02-2012 07:58 AM
How to count multiple values in a single cell, except zero? iuliandonici Excel 1 04-13-2011 09:45 PM
Sum & difference between multiple values in a single cell iuliandonici Excel 4 04-13-2011 09:27 PM


All times are GMT -7. The time now is 03:29 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft