MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formula for rounding down


Posted by Casey on July 03, 2001 10:15 AM

i need a formula that is going to start out being =(a1*b1)+a1

but, i also want to put in there that if the value is .49 or below, to round down to the dollar, and if it is .50 or above, to stay at .50

if i could get the formula and no codes. i don't know code yet.

TIA!!


Posted by Aladin Akyurek on July 03, 2001 10:30 AM

Casey,

Is it not

=ROUND((A1*B1)+A1),0)

what you're looking for?

Aladin

Posted by Sarah on July 03, 2001 10:49 AM

If I understand you correctly, I think this will work for you. In this formula your original formula(=(a1*b1)+a1) was placed in cell C1.

=IF(C1-TRUNC(C1)>0.49,TRUNC(C1)+0.5,ROUNDDOWN(C1,0))

Hope this works.
Sarah


Posted by Casey on July 03, 2001 1:11 PM

it looks like we are on the right track, however, i need the first formula that i gave to be included in the rounding down/up formula. the value that is going to be in cell C needs to multiply A and B, take that total and add it to A. and the value of that formula needs to be rounded down to the dollar if it is .49 cents or below, and rounded to .50 if the value is .50 or above.

: i need a formula that is going to start out being =(a1*b1)+a1


Posted by da on July 03, 2001 1:45 PM

Highlight the cell you typed in sarah's formula and a blank cell next to it. Hit control F. Type in C1. Now hit the replace box. Type in (a1*b1)+a1. Now hit replace all. You can now drop and drag this to where you want.

: Sarah :

Posted by Aladin Akyurek on July 03, 2001 2:34 PM

I see I misinterpreted Casey's question.

The following might be a shorter route:

=FLOOR(((A1*B1)+A1),0.5)

Aladin

=============== Highlight the cell you typed in sarah's formula and a blank cell next to it. Hit control F. Type in C1. Now hit the replace box. Type in (a1*b1)+a1. Now hit replace all. You can now drop and drag this to where you want. : it looks like we are on the right track, however, i need the first formula that i gave to be included in the rounding down/up formula. the value that is going to be in cell C needs to multiply A and B, take that total and add it to A. and the value of that formula needs to be rounded down to the dollar if it is .49 cents or below, and rounded to .50 if the value is .50 or above.

Posted by Sarah on July 03, 2001 3:26 PM

Yeah, that looks like the way to go (NT)

The following might be a shorter route: =FLOOR(((A1*B1)+A1),0.5) Aladin =============== : Highlight the cell you typed in sarah's formula and a blank cell next to it. Hit control F. Type in C1. Now hit the replace box. Type in (a1*b1)+a1. Now hit replace all. You can now drop and drag this to where you want.