LinkBack URL
About LinkBacks
Can this formula be written. If the cells from B1 to E1 contain numbers between 29 & 42 then A1 = to 1 or n/a
Thank you.
Can this formula be written. If the cells from B1 to E1 contain numbers between 29 & 42 then A1 = to 1 or n/a
Thank you.
All of them or one of them ?
All: =IF(SUM(COUNTIF(B1:E1,{">=29",">42"})*{1,-1})=COLUMNS(B1:E1),1,"N/A")
One: =IF(SUM(COUNTIF(B1:E1,{">=29",">42"})*{1,-1}),1,"N/A")
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you, however that did not seem to work. when there were values between 0 & 28 in the four cells to the right of A1, the formula still returned a "N/A". To recap, should the value contained in any cell of the four cells to right of A1 contain a value between 29 & 42 then i need to have a the number 1 returned in A1. If any of the numbers in cells B1 to E1 do not fall between 29 & 42, then i want A1 to remain blank.
See the formula provided earlier for One: (rather than All) - your initial post was quite vague so I provided both.
That worked, thank you! However i am now finding that it may be better if i had "n/a" returned in cell A1, when ever the four cells to the right contain a number between 29 & 42. If there are any other numbers outside of this range then i need A1 to remain blank. Can this be done?
Thank you again.
If you're now saying A1 should return n/a if all four values are within the specified range else blank then alter the earlier All example accordingly by amending the respective TRUE/FALSE outputs:
A1: =IF(SUM(COUNTIF(B1:E1,{">=29",">42"})*{1,-1})=COLUMNS(B1:E1),"N/A","")
Yes that did it, Thank you, very much!
One thing, could you explain the formula to me?
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks