Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-26-2014, 08:30 PM
AUHAMM AUHAMM is offline I need to add multiple values based on multiple criteria in a cell not sure what to do Windows 7 64bit I need to add multiple values based on multiple criteria in a cell not sure what to do Office 2013
Novice
I need to add multiple values based on multiple criteria in a cell not sure what to do
 
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 I need to add multiple values based on multiple criteria in a cell not sure what to do Windows 7 64bit I need to add multiple values based on multiple criteria in a cell not sure what to do Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-27-2014, 08:37 PM
AUHAMM AUHAMM is offline I need to add multiple values based on multiple criteria in a cell not sure what to do Windows 7 64bit I need to add multiple values based on multiple criteria in a cell not sure what to do Office 2013
Novice
I need to add multiple values based on multiple criteria in a cell not sure what to do
 
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 I need to add multiple values based on multiple criteria in a cell not sure what to do Windows 7 64bit I need to add multiple values based on multiple criteria in a cell not sure what to do Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to add multiple values based on multiple criteria in a cell not sure what to do Copy Multiple Rows to new workbook when multiple criteria is met. flds Excel Programming 5 09-30-2014 09:58 AM
I need to add multiple values based on multiple criteria in a cell not sure what to do 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
I need to add multiple values based on multiple criteria in a cell not sure what to do How to count multiple values in a single cell, except zero? iuliandonici Excel 1 04-13-2011 09:45 PM
I need to add multiple values based on multiple criteria in a cell not sure what to do Sum & difference between multiple values in a single cell iuliandonici Excel 4 04-13-2011 09:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft