Need help with FLOOR function
Hi Vishesh,
I have one report in which column U-18, V-18, W-18, X-18 is used to update the scale of the charts. While there is no data in some of the cells in AM columns, If i include AM column for the formula in V-19 V-20, I am getting !NUM# error. This is because this formula first take the minimum value from the range (AC28:AL28) and then -4,3 is used and as there is 0 value in AM28 the result we get from the FLOOR is - hence we get the !NUM# error.
I want your help in building some formula in V19 and V20 (May be ISERROR) so that it can consider any 0 value in the range and did not return !NUM# error. For instance I can say that we need to build a formula in V19 and V20 in such a way that result remains the same (e.g by entering 0 in any cell of the range (AC28:AL28) V19 & V20 should contain result as 30.
I hope I have provided sufficient information. Please see attached test file.
Hoping for best resolution.
Thanks in advance.
Bunty
| Attachment | Size |
|---|---|
| Test.xls | 19.5 KB |
Sorry
Hey Sorry about hacking your Question, but my Question is around floor. Last week at work I used this function to calc. number of for eg. 10 that comes under 100. Could you please let me know if you can give me the formula for it. So to explain properly. I give £5 to every 100th if I have 1000. What formula to use so that it tell me the exact amount in ££? I used floor but can't remember and lost the logic and its really irritating me now. Any help on this is very much appreciated.
I didn't get your question
I didn't get your question but is it simply this ?
(1000/100)*5 = 50
cheers mate got it finally..
cheers mate got it finally.. your first answer was write if i use Int ()..Using floor it was like--=FLOOR(Z5/18,5) just managed, it was doing my headin...
Use Array formula
Try this...
{=FLOOR(MIN(IF(AC28:AM28>0,AC28:AM28))-4,3)}