Solved formula help in excel

June 22, 2015 at 03:26:50
Specs: Windows 7
i have 36 columns with an increasing number in each cell left to right... i.e. range is A1 to AJ1 and the numbers start from 7 in A1 and go up to 286 in AJ1. there is no particular factor by which they grow.

in the range A2 to AJ2 I want to display a value based on the range in A1:AJ1. Going from left to right the first cell in A1:AJ1 that has a number greater 60, I want the corresponding cell in A2:AJ2 to show the number 1. Once a cell in A2:AJ2 has "1" populated, the formula needs to look for the first value greater than 120 in A1:AJ1 and populate the corresponding cell in A2:AJ2 to show the number 1. And then so on and so forth for every value higher than or equal to the next multiple of 60.

any help will be awesome

message edited by bijalvp


See More: formula help in excel

Report •


#1
June 22, 2015 at 07:28:20
Try putting the formula =INT((A1-1)/60) into A2 and dragging/copying it across the entire row.

Report •

#2
June 22, 2015 at 10:35:19
✔ Best Answer
Martcom,

Your solution does not appear to meet the criteria posted by bijalvp. Your solution will put an integer below every value that is above 60.

If I understand the request correctly, bijalvp wants to put a 1 under the first cell that is greater than 60, then put a 1 under the first cell after that cell that is greater than 120 then put a 1 under the first cell after that cell that is greater than or equal to 180, etc.

If I am not mistaken, with values from 7 - 286, there should never be more than four 1's in Row 2.

Assuming that that is correct, I think the following formula accomplishes that goal.

Put this in A2 and drag it to AJ2. Note the placement of the $. It is essential that they be left in place and that no other $ are added. The formula needs to increase the "look back" range as it moves across the columns.

=IF(AND(COUNTIF($A$1:A1,">60")=1,A1>60),1,
IF(AND(COUNTIF($A$1:A1,">120")=1,A1>120),1,
IF(AND(COUNTIF($A$1:A1,">=180")=1,A1>=180),1,
IF(AND(COUNTIF($A$1:A1,">=240")=1,A1>=240),1,""))))

bijalvp: Please note that I attempted to follow your exact criteria which is why the COUNTIF functions for 60 and 120 are different than the COUNTIF's for 180 and 240.

You said...

a number greater 60 and value greater than 120

...and then you said...

and so forth for every value higher than or equal to the next multiple of 60.

Therefore, the COUNTIF criteria for 60 and 120 show ">" while the criteria for 180 and 240 show ">="

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
June 22, 2015 at 10:56:46
Thanks derbydad, that was superb. worked like a charm... i did see my mistake and modified the formula for 60 and 120 to match 180 and it did exactly what I needed...thanks again

message edited by bijalvp


Report •

Related Solutions


Ask Question