Converting percentages to numbers in a formula

[PB624]

Hi All,
Need some help here. I have a spreadsheet that contains a summary tab and multiple sheets with the data. On the Summary I am trying to obtain the following information. The peak utilization per month per store. ON the data tab I have the IN and Out peak utilization for each month in a percentage. On the Summary tab I need to add the two (in and out) percentages from data sheet, divide by size of store and 1) find max percentage and 2) convert to number. EG formula from the Summary tab = Max('2018 Texas'!j6+k6)/c5. That provides the max per store for each month.

Does that make sense.
Thank you in advance.

 

[cerfani]

What's the specific question?

 

[jtakw]

Hi,

A couple of things, a percentage as say, 50%, is actually 0.5 formatted as percentage, so if you want to see the actual number, then in the result cell, format as General or Number will show you the 0.5 rather than 50%.

Also, your =Max('2018 Texas'!j6+k6)/c5 formula, I don't think that actually gives you MAX of anything, it'll just Always be sum of J6 and K6. It's like =MAX(1+2)/3

 

[PB624]

Sorry to be clear. I need the max available products moved to be shown on the Summary tab. Converted to percent. So for example in Jan sold 9.8% of max products that came IN and 9.5% of max that went out. Combining In and Out percents and then summarize for overall products in store and convert to single percent.

 

[PB624]

Excellent, that does help. Thank you.

 

[PB624]

Is it possible to have a formula pre-populated in a cell so when I enter a percentage it automatically converts it to a number? for example cell C5 is actually c3*1000. So when I enter a percent in C5 the formula converts it to a number.

 

[jtakw]

A cell can have either a formula or a manually entered value, not both at the same time, if the cell contains a formula, and you enter a value in that same cell, the formula will be erased and replaced by the value you entered.

 

[PB624]

Thank you. Maybe Office 20XX a guy can dream right!

 

[jtakw]

No, Office 30XX

 

[PB624]

LOL well played.