CEILING and FLOOR Function

---

Query Source   : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by          : DM
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Ok, can someone help me out?  I am trying to create formula that is going to round my numbers to x.47 or x.97.
A1=$9.99
B1=75%
C1=A1-B1=$2.49
So what I am trying to do is to take discount from price and if it ends x.49 I would like to round it x.47 and if it ends x.99 I would like to round it to x.97. Does this make sense….so everything between x.51 and x.99 should be rounded to x.97 and everything between x.01 to x.49 should be rounded to x.47. 

---

Solution:

Assuming the contents as follows
Column A (Actual Price) – A1 = 9.99
Column B (Discount)      – B1 = 75%
Column C (Sale Price)    – C1 = ‘=A1*(1-B1)‘

The Solution would be
Column D (Tag Price)     – D1 = ‘=FLOOR(C2,1)+IF(CEILING(C2,1)-(C2)<0.5,0.97,0.47)‘

---

Example:

---

Show the First Day of Current Week

---

Query Source   :Excel Macros Google Group
Solution Type  : Custom Formula
Query by          : Savio
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
is there any way to have a cell in excel update itself every week to show the first day of the week?
say the week starts on a monday, the cell displays 23/02/09, retains this value thoughout the week and displays 02/03/09 the following monday.

Solution:
1. Select the desired cell.
2. Press F2 and paste the following formula in it.
     =WEEKNUM(NOW())*7*(YEAR(NOW())-2008)+39804
3. press Enter and you’re done with solution.
Example:

---