LinkBack URL
About LinkBacks
I have the following formula =IFERROR(ROUNDUP((+G5+G23+G41+G59)/4,0),"") that if it returns an odd number as in 3 or 5 or 7 or etc i want it to display 4 or 6 or 8 or etc. All help is greatly appreciated.
I have the following formula =IFERROR(ROUNDUP((+G5+G23+G41+G59)/4,0),"") that if it returns an odd number as in 3 or 5 or 7 or etc i want it to display 4 or 6 or 8 or etc. All help is greatly appreciated.
I came up with this quickly. There is possibly something slightly more elegant but it works:
=IFERROR(ROUNDUP((+G5+G23+G41+G59)/4,0)+MOD(ROUNDUP((+G5+G23+G41+G59)/4,0),2),"")
To clean things up a bit, if you put this
=ROUNDUP((+G5+G23+G41+G59)/4,0)
in a separate cell (let's say A2), then your formula collapses to
=IFERROR(A2+MOD(A2,2),"")
This works for one cell
=IF(ISEVEN(A1),A1,A1+1)
So I suppose this would work
=IF(ISEVEN(SUM(G5,G23,G41,G59)/4),SUM(G5,G23,G41,G59)/4,(SUM(G5,G23,G41,G59)/4)+1)
When I put into the spreadsheet where I had a 3 it showed a 2. I changed this portion of the formula /4,0)2),"") to /4,0)4),"") and it showed a 4 so it seems whatever number you have in that blank is what it shows. Thoughts?????????
Tried the new one and it comes back a 3. Seems the +1 par tof that isn't working. Thought???????
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Maybe:
=CEILING((+G5+G23+G41+G59)/4,1) + MOD(CEILING((+G5+G23+G41+G59)/4,1),2)
Or
=CEILING(AVERAGE(G5,G23,G41,G59),1) + MOD(CEILING(AVERAGE(G5,G23,G41,G59),1),2)
Last edited by JBeaucaire; 01-29-2010 at 10:03 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
You may have made an error. I tested this thoroughly and for all sums where your formula shows a 3 (sums 9-12), mine shows a 4. I agree with davesexcel, you need to show us.Originally Posted by tnoble17
Maybe just =IFERROR(CEILING((G5+G23+G41+G59) / 4, 2), "")
Or =IFERROR(CEILING(AVERAGE(G5, G23, G41, G59), 2), "")
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
,
Bookmarks