[Help] Excel formula

[tissuepaper]

Trying to do a reduction of % for every x value but cant seem to get it.
Used excel > home tab >fill > series > columns. Do i seem to be doing it wrong ?

Qn is something like,
actual customers is the no of prospective customers reduced by 20% for every 2km away (rounded down) from the nearest store until it is 0. For eg, a suburb with 58 prospective customers that is 4.5 km away from the nearest store would have 35 actual customers.

Not sure if there will be enough info. but will appreciate some comments for helping me understanding.

TIA

 

[gunners46]

What formula are u using currently?
assuming cell A1 is 58 customers and B1 is 4.5km, then formula for cell C1 should be

=ROUNDUP(A1*(1-ROUNDDOWN(B1/2,0)*0.2),0)

 

[tissuepaper]

haven't really input any formula yet. thought i could do a series and step value of -20% but doesnt work.

kind of stuck at the step. googling for solutions as well. but cant seems to get a good hit yet.

 

[davidktw]

Trying to do a reduction of % for every x value but cant seem to get it.
Used excel > home tab >fill > series > columns. Do i seem to be doing it wrong ?

Qn is something like,
actual customers is the no of prospective customers reduced by 20% for every 2km away (rounded down) from the nearest store until it is 0. For eg, a suburb with 58 prospective customers that is 4.5 km away from the nearest store would have 35 actual customers.

Not sure if there will be enough info. but will appreciate some comments for helping me understanding.

TIA

Wrong answer for below, refer to https://forums.hardwarezone.com.sg/116774446-post7.html for correct answer

Here is another

 

[tissuepaper]

What formula are u using currently?
assuming cell A1 is 58 customers and B1 is 4.5km, then formula for cell C1 should be

=ROUNDUP(A1*(1-ROUNDDOWN(B1/2,0)*0.2),0)

thanks ! it works so far. now checking the solution but so far so good.
distance that are less than 2km will give me the prospective=actual numbers.
more than 2km , im randomly manually checking. where prospective will reduce by 20% accordingly for every 2km.

also trying to understand the formula. but in which part of the formula says a 20% reduction for every 2km until 0km?

correct my understanding, so,
where Roundown(4.5km/2,0) will roundown it to 2, this means the distance cover will be 2km?
0.2 = 20%
58 customers * (-1) * 0.2 = appx 11.6 ?

if distance is less than 2km, eg 1
it will be =roundup(58*(1-Rounddown(1/2,0)*0.2),0)
where rounddown value =0 from 0.6.
and when 0 *0.2 = 0
58* 0 will still be 58 ?
or something like that.

 

[tissuepaper]

Here is another

cant seem to understand this though.

tried input this to help understand a bit,
=IF(58*(1-0.2*4.5/2)>=0;58*(1-0.2*4.5/2);0)
but ans is 31.9
where values are 58 customer, 4.5km

 

[davidktw]

cant seem to understand this though.

tried input this to help understand a bit,
=IF(58*(1-0.2*4.5/2)>=0;58*(1-0.2*4.5/2);0)
but ans is 31.9
where values are 58 customer, 4.5km

I apologise, mistaken the question. This question is quite financial related. It is actually the same as compound interest, except your interest is now making you lose money

Hence following the formula found at https://www.excelfunctions.net/compound-interest-formula.html

I would model the equation to be

= FLOOR([STARTING VALUE]*(1 - 0.2)^(DISTANCE/2))

 

[tissuepaper]

I apologise, mistaken the question. This question is quite financial related. It is actually the same as compound interest, except your interest is now making you lose money

Hence following the formula found at https://www.excelfunctions.net/compound-interest-formula.html

I would model the equation to be

= FLOOR([STARTING VALUE]*(1 - 0.2)^(DISTANCE/2))

okay! thanks man.
in other words, its like $58 losing 20% interest (value) every 2 years

 

[tissuepaper]

now just want to check on another;
eg i have many areas such as jurong, yishun, woodlands, tampines, bishan, hougang, simei etc etc

and a code is assigned to each supermarket, eg ntuc-east =tampines, simei, ntuc-west = jurong, joon koo etc etc

and i have 3 sets (columns) of subscribers to a service for 13,26,52 weeks
hence i will have something like, tampines area > code: east > 48, 35, 32 subscribers to the respective weeks

now i want to sum the no. of subscribers according to east where there are 3 columns of subscribers to 13,26,52 weeks, and 50 rows (areas) of different areas and codes assigned; i used this,....

=SUMIF(B$17:B$53;A4;D$17: D$53)
+SUMIF(B$17:B$53;A4;E$17:E$53)
+SUMIF(B$17:B$53;A4;F$17:F$53)

where A4 = code: east
B17:B53 = all the range of data that are east/west/north/south
where vales in D,E,F are subscribers to 13,26,52

so far the answer add up when i do manually. but is there better way of writing ?
although this is workable.

 

[tissuepaper]

Another qn (a bit silly) :
a staff will receive bonus if they work minimally 1 year. hence, work less than 1 year will not receive bonus. when i do vlookup, it will come out #N/A. I changed the formula to =if(iserror(..vlookup................);0)

and now the 0 is align to the left by default. i want to align to the right, i know can just click align right button.

but is it by default the answer will come out aligned to the left ? if so, just have to manually align it to right ?

 

[davidktw]

Another qn (a bit silly) :
a staff will receive bonus if they work minimally 1 year. hence, work less than 1 year will not receive bonus. when i do vlookup, it will come out #N/A. I changed the formula to =if(iserror(..vlookup................);0)

and now the 0 is align to the left by default. i want to align to the right, i know can just click align right button.

but is it by default the answer will come out aligned to the left ? if so, just have to manually align it to right ?

Cell formatting is not related to your formula.

As for your vlookup issue, I can’t tell. I need to see what you are doing in your excel worksheet.

 

[davidktw]

now just want to check on another;
eg i have many areas such as jurong, yishun, woodlands, tampines, bishan, hougang, simei etc etc

and a code is assigned to each supermarket, eg ntuc-east =tampines, simei, ntuc-west = jurong, joon koo etc etc

and i have 3 sets (columns) of subscribers to a service for 13,26,52 weeks
hence i will have something like, tampines area > code: east > 48, 35, 32 subscribers to the respective weeks

now i want to sum the no. of subscribers according to east where there are 3 columns of subscribers to 13,26,52 weeks, and 50 rows (areas) of different areas and codes assigned; i used this,....

=SUMIF(B$17:B$53;A4;D$17: D$53)
+SUMIF(B$17:B$53;A4;E$17:E$53)
+SUMIF(B$17:B$53;A4;F$17:F$53)

where A4 = code: east
B17:B53 = all the range of data that are east/west/north/south
where vales in D,E,F are subscribers to 13,26,52

so far the answer add up when i do manually. but is there better way of writing ?
although this is workable.

Show your worksheet for better understanding

 

[tissuepaper]

cell selected is C4