CODEWITHSUNDEEP
excelexcel-formula
Rob Dome
Rob Dome

Reputation: 27

Excel Formula to apply one formula to reference call and cells above and another formula to cells below

I'm not sure if this is possible via formula but I am trying to setup a formula that can be applied to a range of cells that will find the middle cell then apply one formula to all cells above it, another formula to the center cell and a different formula to all cells below. I figured out how to find that center cell but when I use an If neither of the conditions seem to be applied. Here is what I have now...

    =IF(INDEX($H$4:$H$204,MATCH((INDIRECT(ADDRESS(((COUNTA($H$4:$H$204))/2)+4,9))),$H$4:$H$204,0)+1,1),0,1)

Upvotes: 2

Views: 158

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

Welcome to SO. Let me know if this is what you're looking for:

=IF(ROW(A1)<ROUNDDOWN(COUNTA(A:A)/2,0),"formula 1",IF(ROW(A1)>ROUNDUP(COUNTA(A:A)/2,0),"formula 2","formula 3"))

The formula will work with both odd and even count of cells.

enter image description here

Upvotes: 1

Related Questions