If formula

Question

Dear My ques is lets suppose there is a column in which a number can come from 0-99 and there is another column in which i've to group these numbers into the range of 0-19, 10-19,20-29.....90-99.

For example

No. No. Range 0 0-9 10 10-19 23 20-29 39 30-39 45 40-49 56 50-59 62 60-69 79 70-79 80 80-89 95 90-99 what will be the formula if i enter any no. from 0-99 it automatically comes in the range defined above. If i enter a no. as 45 it should come automatically come in the range of 40-49 in the column where formula is to be applied.

I hope my answer will be considered and reply will be given.

Thanks & Dheeraj

Answer

Good day,

=IF(A2="","",IF(AND($A2<=9,A2>=0),"0-9",IF(AND(A2<=19,A2>=10),"10-19",IF(AND(A2<=29,A2>=20),"20-29",IF(AND(A2<=39,A2>=30),"30-39",IF(AND(A2<=49,A2>=40),"40-49",IF(AND(A2>=50,A2<=59),"50-59",IF(AND(A2>=60,A2<=69),"60-69",IF(AND(A2>=70,A2<=79),"70-79",IF(AND(A2>=80,A2<=89),"80-89",IF(AND(A2>=90,A2<=99),"90-99","")))))))))))

Hope you appreciate this formula, as you can see it¡¯s really long.

Question

I have two cells. A1 and B1.

I need B1 to stamp today's date when A1 is populated with ANY data. I also need to have that date stay the same so the =today() formula would not be appropriate.

Thank you!!

Rob

Answer

You are right, the =today() formula will not work because the dates will be updated everyday. However, it is not possible forformula to return a date value that will not change.

To solve the problem, we can useVBA to (1) use the worksheet change event on a certain range of cells in column B to convert the formula to text OR (2) create a button that run a macro (when clicked) that populates column B with the current date.

For solution (2), the following macro should be placed in the button code.

   Dim rCell As Range
   
   
   For Each rCell In Range("A1:A5") 'Assumes the range of cells in column A is from A1:A5
   
       If Not (IsEmpty(rCell.Value)) Then
       
           rCell.Range("B" & 1).Value = Date
                
       End If
   
   Next

End Sub

Cheers.

Question

I'm trying to disect this if formula - =IF(F11="","",IF(H11=0,0,IF(K11=0,0,H11/(H11+K11)))). I understand the basics of these types of formulas but this is way over my head. What does the "","" mean??

Answer

=IF(F11="","",IF(H11=0,0,IF(K11=0,0,H11/(H11+K11)))) - translation

if f11 contains nothing then display nothing (two double quotes is an empty string of text) OTHERWISE if H11 is zero display zero OTHERWISE if K11 is zero display zero OTHERWISE calculate h11 divided by h11 plus k11

=IF(F11="","",IF(OR(H11=0,K11=0),0,H11/(H11+K11)))

would do the same thing (and mean the same as above explanation)

sorry for delay in replying - I'm in the uk, and have no internet access at work so this was my first opportunity to reply!