Microsoft Office Forums I need to add multiple values based on multiple criteria in a cell not sure what to do
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-26-2014, 08:30 PM
 AUHAMM Windows 7 64bit Office 2013 Novice Join Date: Oct 2014 Posts: 2
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
#2
10-26-2014, 11:33 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 16,296

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]
#3
10-27-2014, 08:37 PM
 AUHAMM Windows 7 64bit Office 2013 Novice Join Date: Oct 2014 Posts: 2

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
#4
10-27-2014, 09:11 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 16,296

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]

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post flds Excel Programming 5 09-30-2014 09:58 AM pachmarhi Excel 3 07-18-2014 09:57 PM SaneMan Excel Programming 2 02-02-2012 07:58 AM iuliandonici Excel 1 04-13-2011 09:45 PM iuliandonici Excel 4 04-13-2011 09:27 PM

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

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top