Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I get this formula to work? =IF(AT2:AW80<=3.0,AZ2:AZ38+12
Bare with me here, what I am trying to do is, if colums AT2:AW80 are less
than or equal to 3.0, then cells AZ2:AZ38 add 12 to the current number. The spread sheet that I am working on is for attendance, tracking students hours. What I need is when I input there hours in the selected cells I want the cells with there scheduled hours to add 12 every week. If that makes any sense please let me know. If it dosen't then I will try to re-word it some how. Thanks, Mystical |
#2
|
|||
|
|||
Since your ranges are of very different sizes, what you're trying to do
doesn't make sense to me - how are the 79 rows in AT2:AW80 related to the 37 rows in AZ2:AZ38? When you say "if colums AT2:AW80 are less than or equal to 3.0...", do you mean something like "if the sum of columns AT:AW in each row is less than or equal to 3, then add 12 to the corresponding row in column AZ"? One way to treat that would be to put this in AZ2 and copy down: =SUM(AT2:AW2) + IF(SUM(AT2:AW2)<=3, 12, 0) or, equivalently: =SUM(AT1:AW2) + 12 * (SUM(AT2:AW2)<=3) If not, perhaps a better description of how your students' data is laid out and how you enter their hours is in order. In article , "mystical_ways" wrote: Bare with me here, what I am trying to do is, if colums AT2:AW80 are less than or equal to 3.0, then cells AZ2:AZ38 add 12 to the current number. The spread sheet that I am working on is for attendance, tracking students hours. What I need is when I input there hours in the selected cells I want the cells with there scheduled hours to add 12 every week. If that makes any sense please let me know. If it dosen't then I will try to re-word it some how. Thanks, Mystical |
#3
|
|||
|
|||
Here's some code
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "AT2:AW80 " On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < 3 Then .Offset(0, 6).Value = .Offset(0, 6).Value + 12 End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "mystical_ways" wrote in message ... Bare with me here, what I am trying to do is, if colums AT2:AW80 are less than or equal to 3.0, then cells AZ2:AZ38 add 12 to the current number. The spread sheet that I am working on is for attendance, tracking students hours. What I need is when I input there hours in the selected cells I want the cells with there scheduled hours to add 12 every week. If that makes any sense please let me know. If it dosen't then I will try to re-word it some how. Thanks, Mystical |
#4
|
|||
|
|||
It's difficult to add a value to each cell with a single calculation - refer to a single cell in your formula and copy it across the others, if I've read what you mean correctly this should do the trick eg =if(AT2<=3, AZ2+12,AT2) Otherwise you may need an array formula which will get complicated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php...o&userid=26504 View this thread: http://www.excelforum.com/showthread...hreadid=398276 |
#5
|
|||
|
|||
ADDING A VALUE (12) TO THE CURRENT NUMBER (=CURRENT CELL) IS NOT POSSIBLE IN
ORDINARY EXCEL. I BELIEVE YOU HAVE TO USE VBA ( VISUAL BASIC FOR APPLICATION ). REGARDS, SUNAN "mystical_ways" wrote: Bare with me here, what I am trying to do is, if colums AT2:AW80 are less than or equal to 3.0, then cells AZ2:AZ38 add 12 to the current number. The spread sheet that I am working on is for attendance, tracking students hours. What I need is when I input there hours in the selected cells I want the cells with there scheduled hours to add 12 every week. If that makes any sense please let me know. If it dosen't then I will try to re-word it some how. Thanks, Mystical |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
IF / VLOOKUP formula won't work until saved | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions |