• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

=IF statement: formatted into two decimals instead just whole numbers

G

gonefishing12345

New Member
Hello Chandoo Community,


I am a long time reader, first time poster and was a student of the Chandoo Dashboards class.


I am trying to solve for how much months of supply for a product a retailer but also want it to be broken down by either the first or second decimal so if its less than 1 it it will say .77 (3 weeks of supply; now it displays 0. I would greatly appreicate the help. Thank you for your time. Cheers.


Legend: BN5=retailer ending inventory W5=point of sale (POS) and W5+X5+Y5+Z5 are all point of sale monthly data points. A quick example In March you start with 12 units and in March your POS is 3 so you would have 4 months of inventory.


=IF(BN5>=W5,IF(BN5>=W5+X5,IF(BN5>=W5+X5+Y5,IF(BN5>=W5+X5+Y5+Z5,IF(BN5>=W5+X5+Y5+Z5+AA5,IF(BN5>=W5+X5+Y5+Z5+AA5+AB5,IF(BN5>=W5+X5+Y5+Z5+AA5+AB5+AC5,IF(BN5>=W5+X5+Y5+Z5+AA5+AB5+AC5+AD5,IF(BN5>=W5+X5+Y5+Z5+AA5+AB5+AC5+AD5+AE5,9,8),7),6),5),4),3),2),1),0)
 
Hi ,


I am unable to understand your problem ; your IF statement is putting in the values from 0 , 1 , 2 , 3 ,..., 8 , 9 ; how do you want the cell to display 0.77 ?


From where is this 0.77 to come ?


The IF statement is not calculating anything ; it is merely comparing BN5 with various sums , and based on whether BN5 is greater or not , it is putting in integer values of 0 , 1 , 2 ,...


Narayan
 
Hi Narayan,


Think of it like this =IF(inventory>POS March but not greater than POS April there will be a remainder. Right now the remainder is dropped to 0. Thanks.
 
This will be easiest with a helper row. In W4, put this formula and drag to right:

=SUM($W5:W5)

Then, formula to determine your months of supply, with integer being # of whole months and the decimal being amount of next month:

=MATCH(BN5,W4:AE4)+(BN5-LOOKUP(BN5,W4:AE4))/INDEX(W5:AE5,MATCH(BN5,W4:AE4)+1)
 
Hi Luke M,


I tried that but no sure if my data would work using the =sum(W:5:W5) since I have so many rows. Enclosed is a screen shot of some of the data. Thanks. And the new formula of the beginning row. Thanks again.


=IF(AT5>=J5,IF(AT5>=J5+K5,IF(AT5>=J5+K5+L5,IF(AT5>=J5+K5+L5+M5,IF(AT5>=J5+K5+L5+M5+N5,IF(AT5>=J5+K5+L5+M5+N5+O5,IF(AT5>=J5+K5+L5+M5+N5+O5+P5,IF(AT5>=J5+K5+L5+M5+N5+O5+P5+Q5,IF(AT5>=J5+K5+L5+M5+N5+O5+P5+Q5+R5,9,8),7),6),5),4),3),2),1),0)
 
I will mimic it here.


A B C D E F H

Forecast POS Retailer EI Inventory Number of months supply

March April May June July As of March 4 Forumula =IF(F:3>B3,if(F:3>B3+C3..

14 24 34 56 23 62 4.42


How can I post a portion of my spread sheet here. Thank you for your help.
 
When you paste the data in, put a backtick at beginning of the data and at end. Backtick is the key right above the left Tab key.
 
Can you put the helper data on the side then? Maybe starting in col BA? It can be done, but it's much harder to figure out different rolling SUMS within a single formula.
 
Hi Luke,


I tried to use the ` methodology to add the data here but doesnt come over cleanly. Its okay, I'll try a different forum. Thank you for your time. I really appreciate it.
 
You must log in or register to reply here.
Back
Top