|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
||||
|
||||
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] |
#3
|
|||
|
|||
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
|
||||
|
||||
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] |
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 |