IF & AND formula with cell ranges

[sarahscott424]

Hi

I am currently using the following formula in cell B1, which does what I want it to do if I am only looking at one cell:

=IF(AND(9<A1,A1<13),0.5,"0.25")

Basically, this gives me the answer 0.25 in cell B1 if the number in cell A1 is less than 9, or gives me 0.5 if the number in the cell is between 9 and 13. So good so far.

The problem I have encounterd is where I want the formula to consider a range of cells instead of just one. For example, I would like the formula to consider what is in cells A1:A10 and if any of the cells is less than 9 then 0.25 would be added into cell B1 or 0.5 would be added if any thing was between 9 and 13, which may result in B1 containing a number such as 1.25 or 4.75. As a result, B1 would contain a running total of 0.25 and 0.5 in every instance where the criteria is met. Does that make sense? I hope so. Please help!

[Pete_UK]

Well, I'm surprised that the formula gives you what you want, as you are returning "0.25" and the quotes means that you have a text value rather than the number 0.25. Besides, you would get "0.25" if A1 is above 13.

Anyway, you could try this array* formula in B1:

=SUM(IF((A1:A10>9)*(A1:A10<13),0.5,0.25))

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

[sarahscott424]

Well, I'm surprised that the formula gives you what you want, as you are returning "0.25" and the quotes means that you have a text value rather than the number 0.25. Besides, you would get "0.25" if A1 is above 13.

Anyway, you could try this array* formula in B1:

=SUM(IF((A1:A10>9)*(A1:A10<13),0.5,0.25))

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

Thanks for your response Pete, you're right though, the more I test my original formula the more I realise it doesn't work (plus I just realised my answers for the formula to give were wrong)! What a mess!

To explain what I am trying to do, the numbers in column A represent the hours worked in one shift. Where the shift worked is longer than 8 hours, I need to deduct 0.5 hours, but where the shift is 8 hours or less I need to deduct only 0.25 hours. How might I do that!? I need a number value returned once the test has been run on each cell, with the number being added to the previous result. So if the range was A1:A3 where A1 = 12, A2 = 3 and A3 = 10 the answer in B1 should be 1.25 (0.5 for A1, 0.25 for A2 and 0.5 for A3)

[uselessnut]

Hi

Try this

In Cell B1

=IF(AND(9<A1,A1<13),0.5,0.25)

Drag the Values / Copy from Cell B1 and paste from B2 to B10

In Cell B2

=IF(AND(9<B1,B1<13),0.5,0.25)

Likewise in Cell B3 to B10

In Cell C1

 =Sum(B1:B10)

This should solve your problem.

[Ron Coderre]

Maybe this regular formula?

=SUMPRODUCT((A1:A10<>"")*((A1:A10>=9)*(A1:A10<=13)*0.25+0.25))

or this

=SUMPRODUCT((A1:A10>=9)*(A1:A10<=13)*0.25+0.25)

Is that something you can work with?

[sarahscott424]

Thanks for your response Pete, you're right though, the more I test my original formula the more I realise it doesn't work (plus I just realised my answers for the formula to give were wrong)! What a mess!

To explain what I am trying to do, the numbers in column A represent the hours worked in one shift. Where the shift worked is longer than 8 hours, I need to deduct 0.5 hours, but where the shift is 8 hours or less I need to deduct only 0.25 hours. How might I do that!? I need a number value returned once the test has been run on each cell, with the number being added to the previous result. So if the range was A1:A3 where A1 = 12, A2 = 3 and A3 = 10 the answer in B1 should be 1.25 (0.5 for A1, 0.25 for A2 and 0.5 for A3)

[Ron Coderre]

This regular formula begins the sum of "adjusted hours"

B1: =A1-(A1>0)*LOOKUP(A1,{0,9},{0.25,0.5})

and this regular formula, copied down, continues the sum

B2: =B1+A2-(A2>0)*LOOKUP(A2,{0,9},{0.25,0.5})

With A1:A3 containing these values
9
8
8

These would be the returned values:

8.50
16.25
24.00

Is that something you can work with?

[sarahscott424]

This regular formula begins the sum of "adjusted hours"

B1: =A1-(A1>0)*LOOKUP(A1,{0,9},{0.25,0.5})

and this regular formula, copied down, continues the sum

B2: =B1+A2-(A2>0)*LOOKUP(A2,{0,9},{0.25,0.5})

With A1:A3 containing these values
9
8
8

These would be the returned values:

8.50
16.25
24.00

Is that something you can work with?

Thanks for trying to help me work this out! Whilst I see what your formula does, it wouldn't be very practical for my spreadsheet. The formula needs to be applied to a huge cell range therefore I want to try to avoid having duplicated cells with the "adjusted hours" in them. Ideally, I would like the formula to look at the huge cell range and provide the answer in one cell as a single figure (which will be made up of lots of instances of 0.5 and 0.25 added together).

[Ron Coderre]

Thanks for the clarification...
This regular formula sums the adjusted hours for the referenced range:

B1: =SUMPRODUCT(A1:A10-(A1:A10>0)*LOOKUP(A1:A10,{0,9},{0.25,0.5}))

Is that something you can work with?

[sarahscott424]

Thanks for the clarification...
This regular formula sums the adjusted hours for the referenced range:

B1: =SUMPRODUCT(A1:A10-(A1:A10>0)*LOOKUP(A1:A10,{0,9},{0.25,0.5}))

Is that something you can work with?

Ron - you are a genius! That does work, but instead of the answer being the total of the hours as adjusted, how do I alter the formula so that the answer is the total of the adjustments to be deducted from the hours?

[GIS2013]

@Hi sarah, try to click the button "calc" I added to the enclosed file.
HIH

Dear seniors here, is it wrong to combine in my replies here vba code?

[sarahscott424]

@Hi sarah, try to click the button "calc" I added to the enclosed file.
HIH

Dear seniors here, is it wrong to combine in my replies here vba code?

Unfortunately that doesn't seem to work...

[GIS2013]

Here is the code, oops I named the sheet "calc" it must be "sheet1" in your case, isn't it?
update - I've checked it and it works fine in my computer.. Do you see the button called "calc"?

Worksheets("calc").Activate

Set Rng = ActiveSheet.Range("A:A")

lr = ActiveSheet.UsedRange.Rows.Count

For i = 1 To lr

If Cells(i, "A").Value > 9 And Cells(i, "A") < 13 Then

ActiveSheet.Cells(i, "C").Value = 0.5

Else: ActiveSheet.Cells(i, "C").Value = 0.25

End If

Next

Range("b1").Value = Application.Sum(Range(Cells(1, 3), Cells(lr, 3)))

End Sub