Excel If field with rounding

0 votes
I'm average at Excel and this one has me stumped.

What I'm trying to do is calculate if a cell is 0 return a figure of 0, if the cell is greater than 0 but less than 61 (eg 60) return .15, if the cell is greater than 61 then divide the cell by 60 and round up to the next whole number eg if the cell is 86 the figure returned from the divide is 1.433 so I want that rounded to 2.

Can someone please help me with this?
Sep 26, 2022 in Others by Kithuzzz
 • 38,000 points • 820 views

1 answer to this question.

0 votes

Here's your formula. Assumes your cell containing the value to be evaluated is A2.

=IF(ISNUMBER(A2),IF(VALUE(A2)<=0,0,IF(VALUE(A2)<=61,0.15,ROUNDUP(VALUE(A2)/60,0)*0.15)),"0")

Here are the test results for all your conditions.

formula-results

answered Sep 27, 2022 by narikkadan
 • 63,600 points

Related Questions In Others

0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
 • 63,600 points • 1,592 views
0 votes
1 answer

Help needed with Median If in Excel

Try entering the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another ...READ MORE

answered Nov 10, 2022 in Others by narikkadan
 • 63,600 points • 900 views
0 votes
1 answer

Excel - IF combined with Index Match

Try: =IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!$C$2:$E$10,3,FALSE),""),"") =IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!$E$2:$E$10,MATCH(A2,Sheet2!$C$2:$C$10,0)),""),"") where the first "" means that O2 is not a number, ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
 • 63,600 points • 745 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
 • 63,600 points • 936 views
0 votes
1 answer

Equivalent of Excel Round function

Here's a sample LINQPad program that demonstrates one way ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
 • 63,600 points • 1,083 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
 • 63,600 points • 1,023 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
 • 63,600 points • 1,569 views
0 votes
1 answer

(Excel) If cell is greater than <condition> then minus <number>

The IF function to calculate B5 (amount ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
 • 63,600 points • 1,434 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
 • 63,600 points • 3,542 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
 • 63,600 points • 722 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.